Travelling Again … Unfortunately

For the second time in less than a month, I found myself flying to a different corner of the United States, this time the Northeast.  Ok, while Pennsylvania may not be in the far northeast corner of the country, it is still over 1000 miles away.  This time it was not for pleasure.  Rather it was for a death in the family.

Unfortunately, there no longer is a direct flight from Orlando, FL to Allentown, PA (Yes, the Allentown from the Billy Joel song).  So we had to make a stop in Charlotte, NC.   Actually, we felt quite lucky to grab tickets on Saturday for a flight the next morning.  It was sort of a coincidence that Charlotte is also the host city for next year’s PASS Summit.  Having just come back from this year’s PASS Summit in Seattle, WA, I suppose the coincidence only means something to me.

The flight to Allentown was uneventful.  We arrived in time to witness my wife’s father take his last breath at the hospital.  He had a massive coronary a few days before.  There was nothing the doctors could do.   It was a little surreal watching the monitors drop to zero understanding that every line on the monitor represented a different body function that was shutting down.  It was the first time I ever witnessed someone actually die.  I suppose that it is of some comfort that at an age of 83, he led a good and full life and died rather peacefully.  Still it is hard to get my head around the fact that he will no longer be there.  Perhaps our daughter, Natasha, said it best when she wrote and gave the following eulogy at the burial.

Say not in grief ‘He is no more.” But in thankfulness that he was.

Grandpa, on this day before Thanksgiving, I am thankful for the days I spent at your house before I was old enough to go to school, eating tomato soup and watching The Price Is Right.

I am thankful for every birthday that you flew down to Florida to celebrate with me bringing what seemed like an entire suitcase full of presents for us because you missed us so much.

You were a man of humble beginnings, but you always strived to better yourself.

You graduated from high school at a time when most people dropped out or were forced to go to work to support their families.

You inspired and supported your daughter and granddaughter to go to college.

We learned from you that you have to work hard to succeed in this world.

You will be with me in all the big events in my life, and even the small ones.  I will always remember going to the ATM with you as you wondered why English and Spanish were the only options and not Pennsylvania Dutch, and I will remember when I use a microwave your amazement of how that tiny box can cook food so fast.

So today, I say not in grief,  that he is no more, but in thankfulness that he was a part of my life and will always be in my heart.

We got back from Pennsylvania late Saturday night after repeated delays in our flight out of Allentown.  I wish the delays were something exciting like snow, but rather it was just some mechanical malfunctions that needed to be checked out.  On the other hand, we did get to see snow flurries on Saturday, something we had not seen in years due to living in Orlando, FL.  Maybe that was his final gift to us as we prepared to leave for home.

We will miss you Dad.


Thanksgiving, KPIs and Other Miscellaneous Notes

Happy Thanksgiving to those of you celebrating Thanksgiving in the United States this week.  Remember Thanksgiving, it’s the holiday before Black Friday.  You know, the holiday between The Great Pumpkin and It’s a Charlie Brown Christmas.  Yeah, sort of the forgotten holiday.  Have a Happy Thanksgiving anyway.

Some of you may know that when I was at the PASS Summit last week I talked to some of the Microsoft folks about a problem I noticed when defining KPIs using the version of PowerPivot that comes with Office 2013 and even the KPIs used in the Tabular model of SSAS in SQL Server 2012.  It appears that when you define a KPI and associate an icon set with the KPI definition the selected set does not ‘stick’ with the definition.  Rather you always get the first set consisting of a red circle, a yellow circle and a green circle.  Functionally the KPI works correctly assigning the correct colored circle to each of the ranges in the KPI.  It just does not ‘remember’ which icon set you selected.  Well of course you should expect that considering these two products share some of the same DLLs.  So it should not be a really big surprise that if it does not work in one, that it might not work in the other.  (Yes, it did work in the original release of PowerPivot for Excel 2010 that came out about two years ago.)

Anyway, after demonstrating this problem to the Microsoft folks, they promised to get back to me, and they did on Thursday of this week.  They suggested that it may be a problem with the community release and that the final release of Office 2013 and perhaps even SP1 of SQL Server 2012 may fix this.  I intended try upgrading my demo machine after SQL Saturday this past Saturday (Tampa BI edition:  Because I was scheduled to do two presentations, I really did not want to take a chance of last minute changes to my machine until after the presentations.  I would have been partway into updating my machine as you read this on its publish date, but something serious has come up which will delay me at least a week.  It also probably means that I will not have a blog post this Saturday.  However, it is Thanksgiving and many of you probably are still recovering from all that Thanksgiving eating and shopping.

So, unless someone else beats me to it and tests the KPI icons with SP1 of SQL Server 2012 and posts their results as a comment to the blog, I will probably not get to try this until sometime next week.  I also hope you will give me a pass at missing this coming Saturday’s post, but it will all become clear next week when I expect to resume my normal schedule of postings on Tuesdays and Saturdays.

Until then, C’ya.

Using the SSAS Tabular Model, Clean-Up in Week 7

Last time, I left our pivot table looking like the following image with a few minor problems that needed to be fixed including:

The month names were listed alphabetically rather than chronologically.

Some months may display error messages when no data exists in the current or previous month.

To fix the month order, I am going to return to the SSAS model.bim page and add a calculated column to the Date table.  In this column I am going to use the MONTH() function to return the month number of the current row’s DateKey value.  In the figure below, the first several rows of data occur in the month of July and thus the function returns the number ‘7’.  In the case, the day and year do not matter.  I just need to know the month number so I can sort the month names by the month number to get them in the correct order.

After creating the month number column, I go back to the CalendarMonthLabel column and select the entire column by clicking on the column header.  Strictly speaking I would not have to select the column first.  However, by doing so, the resulting dialog box automatically knows to use that column as the column that I want to display.

Next, open the Column drop down menu and select the Sort option.  This opens a secondary fly out menu with sort options.  From this menu, select the Sort by Column option as shown below.

The Sort by Column dialog already knows the column that I want to display (on the left side), but prompts me for the column name by which I want to sort the label.  On the very bottom of the drop down list is the field I just added.

When I select the field to sort by and refresh my Excel pivot table, the data appears sorted correctly with the months now appearing in a chronological order.  But, I still have a problem with January displaying an error message for 2007 data because there is no 2006 data in this database.

Some people who have been building PowerPivot tables may not realize that DAX supports several functions that can be used to trap errors and resolve display issues like the one above.  For this week, I’m just going to focus on two DAX functions, IF() and ISBLANK().  The IF() function works much like you probably expect.  It begins with a expression as the first parameter that evaluates to a Boolean value.  If the expression evaluates to TRUE, the second parameter is evaluated and its result is returned from the function.  If the expression is FALSE, the third parameter is evaluated and returns its result from the function.  In this case, the expression I want to evaluate is whether StoreSales is blank or if LastPeriodSales is blank.  Either or both of these measures could be blank if there were no data records for the current month or previous month respectively.  Note that this is not the same as records having a value of zero.

Since I want to return a value of ‘0’ if either of these two measures are blank, I chose to use the expression:

ISBLANK([StoreSales]) || ISBLANK([LastPeriodSales])

This expression checks if the measure StoreSales is blank OR the measure LastPeriodSales is blank.  Had I used the connector && rather than ||, I would be asking if the StoreSales measure and the LastPeriodSales measure were both blank.  Since I only need to know that one or the other is blank, I use the ‘OR’ connector: ||.

Putting this all together, I get the expression:

SalesGrowth:=IF(ISBLANK([StoreSales]) || ISBLANK([LastPeriodSales]), 0, ([StoreSales] – [LastPeriodSales])/[LastPeriodSales])

Perhaps displaying a zero where either of these values is not defined is not exactly what I want.  Afterall, a growth rate of 0% means something entirely different than an undefined value.  Rather, I want to display a blank in the field if the value for StoreSales or Last Period Sales does not exist rather than a value of ‘0’.  This small change has the added benefit of removing the status icon from the final column when either sales value is not defined.  To return a blank, use the BLANK() function as the True argument in the IF expression.

Refreshing my Excel pivot table one last time I now have a properly ordered and formatted table as shown below.

C’ya next time as I explore more interesting technology features in Microsoft’s current crop of products.

The 2012 PASS Summit, Flying, and More

Last week I was at the 2012 PASS Summit in Seattle WA.  It was everything that others told me it would be.  I enjoyed every minute of it.  Of course, the best part for me was the opportunity to give one of the sessions.  Even though it was the last session on the last day of the conference, it was still very well attended, and I was relieved that at least some people stayed around until the end to hear my talk.  I met lots of interesting people from around the world and hopefully, some of them will be joining me in my blogs or on LinkedIn ( over the next several weeks to see what else I have to say about DAX, PowerPivot, and the Tabular Model.  The bottom line is that I recommend any serious professional try to attend a major conference in his or her field of work at least once a year.  The networking and learning opportunities are well worth it.

Let’s talk about the flight now.  I’m not a fan of sitting in a long metal cylinder (soon to become carbon composites apparently according to the one ad on the flight), crushed between strangers sitting in a seat designed for 8-year olds.  I mean really, did they ever test these seats with people taller than 5’6”?  At 6’3” my knees are wedged into the back of the seat in front of me. Then the lady (and I use that term loosely) in front of me kept trying to push her seat back against my knees attempting to push my femur right through my pelvis and into the back of my seat, it was a painful five hours getting from Chicago to Seattle.  After getting there I almost could not walk and my legs nearly collapsed under me twice just trying to get out of the plane.

And whatever happened to food on cross-country flights?  I probably will date myself, but I remember the days when you got a hot meal with real metal utensils.  Now they cannot even afford to give you a bag of pretzels.  Well actually, Jet Blue still does.  By the way, to the person eating burritos and beans somewhere around me, take note for the next time that menu item is not a good choice for a sealed in environment.

My flight did not even have ‘free’ TV.  Not even network channels.  On the flights west, they had little 5 in screens (or at least that is the size they seemed to be) that folded down from the luggage rack that displayed DirectTV in color.  By color, I mean that each screen going up through the plane was a different hue.  And they were not even in front of each row of seats.  The one in front of me was about 4 rows up.  I would have needed a telescope to watch anything.  Coming back, the plane had 3” TV screens in the back of the seat in front of each person.  But again, they wanted $7.99 just to watch a few shows on DirectTV.  This was a red-eye flight back leaving Seattle at 10:50 PM so I don’t think they made much money on that flight.  Oh, by the way, Jet Blue still lets you watch basic programming for free.

And each flight I was on seemed to be overbooked.  Are there really that many people travelling or have they cut back on the number of flights?  I mean no wonder the airlines are losing money when they offer a bonus of $500.00 for a person to wait for a later flight (about 4 hours later) and get a free upgrade to first class on that later flight.

I guess it could have been worse.  I hear that Spirit charges up to $100 each way for each luggage bag.  When it costs more to send your luggage than yourself, how long will it be until people try to wear 10 changes of clothes on top of each other to avoid the luggage fees and then try to pull those seat belts around their 15 pairs of pants and actually snap the two ends together.

At one time, flying was elegant.  But no longer.  Now between the lines at TSA, boarding lines, and overbooked flights, it feels to me more like cattle on the way to slaughter or maybe waiting in a line for a 3-minute ride at one of our local theme parks.

Finally, I find it amusing when the crew says on your way off the plane.  “Thank you for flying with us and we hope to see you again.”  In the meantime, I am sure that we are all thinking, “Thank God I’m getting out of this box and can breathe real air again that won’t pop my eardrums.

Fortunately, next year the summit is in Charlotte, North Carolina.  If I get the opportunity to go (and I hope I do) It would be a bit of a long road trip, but I’m thinking maybe it would be better to just drive.

C’ya all next time.

Using the SSAS Tabular Model, Week 6 – KPI

In previous weeks, I explained how to build a Tabular model that in many ways produces the same results as some of my previous blog entries in which I used PowerPivot for Excel.  This week I continue by showing how to create KPIs within the Tabular model.

Let’s assume that the management of Contoso is interested in looking at the change in sales from one month to the next specifically within their ‘stores’ channel.  To do that, I will begin by creating a new measure that calculates store sales using the CALCULATE() function.  The advantage of nesting the SUM() function (which you might have thought of using) inside the CALCULATE() function is that CALCULATE() enhances the function used as the first parameter by adding a second parameter that applies a filter to the data.  In this case, I want to filter the data based on the channel type ‘Store’ found in the table Chanel and field ChannelName.  The following figure shows the expression used to define the measure StoreSales.

If my intent is to show sales by month in my pivot table, then I need to create a second measure that calculates the sales from the previous month.  While the first expression did not reference a time period like month in the filter, I will actually apply a filter of month to my pivot table by making month the row dimension.  Unfortunately, this does not automatically translate into calculating data from a prior month without some additional DAX help.  However, I can use the CALCULATE() function again applying it to the StoreSales measure I just calculated and applying a filter to get only the previous month’s data by using the DATEADD() function as shown in the following figure.

Finally, given the sales for the current month and the previous month, I can calculate the growth in sales.  Rather than display growth as a dollar amount, it makes much more sense to see growth as a percent change from the previous period.  In other words, if sales were hypothetically $200 in one month and $208 in the next month, I would have a 4% growth as calculated by the expression:

(208 – 200) / 200 * 100 = 4%

The following figure shows the corresponding DAX expression to calculate the sales growth measure.

If you look at the Measure grid, you can see these three new measures.  By default, the calculated values are displayed as decimal numbers.  I could place these measures as they are on a pivot table (and I will as you will see later) and then format the numbers as currency or percentages.  However, each time I use these measures, I would have to format the values again.  If instead, I define the format property of each of these measures in my model definition, I will not need to format them each time I use them.  To do this, click on each measure one at a time and modify the Format property.  In the first figure below for the measure StoreSales, I define the value as Currency. This format option displays a currency symbol at the left with commas separating thousands and two decimal places.

Similarly, I can format the LastPeriodSales measure as Currency and SalesGrowth as Percentage.

Next, before I apply these measures to my pivot table, I want to add a slicer to the table to only look at data by year.  Slicers are available from the Analyze ribbon in the PowerPivot Tools group of Excel specifically in the Filter group.

From the Insert Slicer dialog, open the table containing the dimension you want to use in the slicer and then click in the checkbox before the name of the field you want to use.  In this case, I want to use CalendarYear as my slicer as shown in the next figure.

With my slicer added to the pivot table, I add my three measures to the values area.  By choosing a year in the CalendarYear slicer, I can see the store sales for the year 2008.  The Last Period Sales (assuming that I use the previous month calculation) shows me a slighted strange time period of December 2007 to November 2008.  Finally, the Sales Growth shows the percent growth (or shrinkage) for these time periods. Now before you say anything, I still want to change the row dimension that I’m using to be Years, then Months.  But I’ll do that later.

First, let’s define the KPI for the SalesGrowth measure.  Returning back to SSAS and the model.bim tab, I right click on the SalesGrowth measure to display the popup menu shown below.  The option from this menu that I want to use is Create KPI.

The Key Performance Indicator dialog that appears next begins by having us define the target value.  KPIs always compare a measure to a desired value.  Sometimes that value needs to be another measure (perhaps you want a certain market penetration based on the total population of the areas where you have stores).  Sometimes that value can be compared to an absolute value.  In this case, I want to compare the value to 0.0% which represents no growth (or loss) in sales.  Numbers greater than zero indicate growth.  Numbers less than zero indicate losses in sales.

Next, I can select whether I want to use a three or five band scale.  If I select a three band scale, the typical use is to indicate that high values are good or low values are good.  Five band scales on the other hand generally are used to indicate the middle values are good or extreme values are good.  In the target bar, the dialog shows a small text box above the boundary of each color change.  In this text box, I can enter a value to indicate when the status of the KPI should visually change.  For example, in the following figure, I entered a lower value of -0.1 and an upper value of 0.1.  These two values a decimal numbers correspond to -10% and 10% respectively.  If I wanted to my band to indicate deviations of 5% from the central value of 0.0, I would enter the lower value of -0.05 and an upper value of 0.05.

After selecting the number of bands and their order, I can select from a variety of icon styles to display at a glance the status of the KPI.

After finishing the definition of the KPI by clicking OK, I can refresh my pivot table in Excel to display the KPIs group as shown in the following figure.  Each KPI that I define is listed under the group KPIs.  By expanding the individual KPI values, I can get access to the value, goal, status, and trend.  In this case, the value is the percent growth of sales.  The goal is 0% and is probably not something I would display in this case.  However, the Status value will display the icon selected the KPI dialog I shown above.

The following figure shows my revised pivot table.  However, it still shows the product hierarchy as my rows.

Changing the product hierarchy to CalendarYear and then CalendarMonth, my pivot table begins to make more sense.  In fact, you can see where the store sales for any given month in the year displayed (2007 in the following figure) is the same as the value for the field Last Period Sales in the subsequent month.  In other words, June Store Sales are the same as July’s Last Period Sales.  This is exactly what I want.  Only a couple of problems are left.  First, the months are listed alphabetically rather than chronologically.  Second, the columns where there is no data for a previous month such as January of 2007 display an error (#NUM!) for the Sales Growth but displays a green status indicator.

Next time, I show you how to clean up these minor problems as I finish out this series.

C’ya then.

Election Day and the PASS Summit

Today is Election Day in the United States and I hope all of you who live here have or will take the time to go out and vote.  I live in a state that has early voting so I was able to cast my vote last week, which is a good thing because I am travelling today.  Voting is a privilege that we all should take seriously.  It is not a ‘right’ as some would have you believe because rights cannot be taken away and they are not granted by man or man’s government.  You may not always get want you want when you vote, but at least you have the opportunity to express your opinion.  In fact, I do not believe that anyone should complain about the government if they did not take the time to vote for each of the candidate positions.  By voting, I feel I have a ‘right’ to complain when politicians go back on their promises or make things worse like increasing the deficit or creating more unemployment or underemployment.  Therefore, if you did not get out of your chair to go vote, I do not want to hear your complaints.

Today I am actually on a plane heading for the great northwest, Seattle Washington, for the SQL PASS Summit.  I am not a fan of travel or of most airlines.  It feels like they are herding cattle.  I am about 6’3” and the space between rows of seats is just not enough in most planes.  Who designs the seats anyway?  Contortionists?  My knees are usually up against the back of the seat in front of me and if they try to push their seat back I feel like I’ve just been placed in a coffin with the lid closed.  The only airline I like is Jet Blue, but I got stuck on a United flight so next week I’ll tell you more about the fun of flying United, assuming that I can stand up and walk off the plane.

Anyway, I am going to the PASS Summit as I mentioned above.  I have never been to the Summit even though I have used SQL Server for 15 years now.  I am going because I have been lucky enough to be chosen as a speaker for this year’s summit.  I am speaking on using the DAX language.  Interestingly, DAX, as those of you who have been reading my technical blog entries on Saturdays know, is the language of not only PowerPivot, but also the Tabular mode of SQL Server Analysis Services.  It will be interesting to see what others attendees are doing with Business Intelligence and Analysis Services.

It should also be interesting to see some of the things around Seattle.  There is Pike’s Fish Market and the Seattle Needle among other things close to the convention center.  My wife Susan is going along.  We planned this a few months ago and her current illness will not stop us.  BTW, the news was not great from her tests.  In fact, it was rather bad, and we will have to come up with a plan of action when we get back next week.  I just hope that she will not have any bad painful days while we are away.

I checked the weather forecast for Seattle and surprise, it calls for showers every day that we are there.  I do not know what showers mean in Seattle.  Here in Florida it means that it might rain for 10 or 15 minutes sometime during the day and be sunny the rest of the day.  It is also about 10-20 degrees colder than here in Florida although the difference between the high and low temperature is not as dramatic as here.

In any case, it should be an interesting and hopefully a fun trip.  If any of you who may be reading this will also be at the PASS Summit, stop by my presentation on Friday afternoon to say, ‘Hi!’

C’ya next week.

Using the SSAS Tabular Model, Week 5 – Hierarchies 2

Last week I showed you how to build a hierarchy for the Date table that included the fields Calendar Year, Calendar Quarter, and Calendar Month.  All three fields were in the Date table so the process was easy.  What happens however, when the fields for the hierarchy are in different tables?  I cannot create a hierarchy using fields other than those in the same table.  The answer is to simply create a calculated field in the table where you want to build the hierarchy based on the fields from the other tables.

The hierarchy that I want to build today includes the product category description, the product subcategory description and the product description.  This hierarchy defines the grouping of products first into subcategories and then into broader categories.  Currently these fields reside in three tables as you can see in the following figure.

To add calculated columns in to the Product table that will display the ProductSubcategoryDescription field from the table ProductSubCategory and ProductCategoryDescription from the table ProductCategory, I need to return to the grid view of my model.

Next, I need to select the Product tab to display the fields in the Product table.  Using the horizontal scroll bar at the bottom of the grid, move to the right end of the table.  You should see one more column named Add Column.  Click on this heading to select the column.  (Note: if you have a table with a large number of columns, you can also jump directly to the same point as above by clicking on Add Column in the Column drop down menu.)

Right click on the text Add Column and choose the Rename Column option from the menu.  I will rename this first calculated column Product Description

After typing in the name and pressing Enter, SSAS takes a few seconds to create the column in the model.  When it is done, I can enter the formula for the calculated column.  In this case, I simply need to reference the ProductCategoryDescription field from the ProductCategory table.  However, because I am referencing a field in another table, I must use the RELATED function so that I get the product category description for the product in each row.  I can just start typing and my formula should appear in the formula bar area.  However, if for some reason SSAS has lost focus on the new column, you can always click in the first row of the new column and begin typing the formula.  My formula in this case is:

= RELATED(ProductCategory[ProductCategoryDescription])

Note that SSAS also allows me to take advantage of the autocomplete feature we saw previously when creating DAX formulas in PowerPivot.  I strongly recommend taking advantage of this feature if for no reason other than it eliminates spelling inconsistencies.

After completing the formula and pressing the Enter key, it takes a few moments for SSAS to calculate the values for all the rows in the calculated column.

Similarly, I want to create a calculated column to reference the ProductSubcategoryDescription field from the ProductSubcategory table.  The steps are similar so if you are following along, I’ll let you try it on your own.

The right most columns of your product table should look something like the following:

Now you want to switch back to the diagram view mode and create the hierarchy just like we did last week.  When you are done, it should look something like the following:

Because we have the hierarchy defined for product category -> product subcategory -> product, we do not need to show the individual fields to the user if we want them to always use the hierarchy.  To hide the fields from the PivotTable Fields panel in Excel, right click on the field in the diagram view of SSAS and select Hide from Client Tools.

Repeat this procedure for the three fields:

Product Description

Note: Do the above only where these three items are represented as fields, not where they are defined in the hierarchy.  Also, do not delete these fields from the model.  If I delete these fields, they cannot participate in the hierarchy.  Hide these fields from the client tools only.

You can do the same thing with the two tables:


Since the only information I need from these two tables now is shown within the Product table, I do not have to display these tables to the end users of the pivot table either.  To hide an entire table, right click on the table name in the diagram view and select Hide from Client Tools.

Next, I redisplay the Excel spreadsheet with the pivot table I have been working on and refresh the data by using the Refresh button found in the Analyze ribbon.  I see that the PivotTable Fields list displays the product table with my hierarchy first, then a entry: More fields which when expanded shows all the other fields in the Product table.  To use the Product hierarchy, I can either select it using the checkbox at the beginning of the field or I can click on the hierarchy name and drag it down to the Rows area (or the Columns area).

As I might expect, I can now expand and collapse portions of the pivot table to see the data that I am most interested.

One last point I want to make for this week.  SSAS and PowerPivot can work with both Star and Snowflake schemas.  A Star schema is one in which there is a central fact table with one or more dimension tables that radiate out from it sort of like the following figure.


A snowflake schema can have dimension tables that also have additional dimension tables that radiate out from them.  My original model is a snowflake schema because the Product dimension has a Product Sub Category dimension that radiates from it and the Product Sub Category dimension has a Product Category dimension that radiates from it.  Similarly, the Stores dimension has two additional dimensions that radiate from it, Geography and Entity.

Generally, the closer your model is to a star schema, the better the performance… usually.  That statement is not an absolute because while working with a model having fewer tables results in better performance, the remaining tables grow in size with ‘duplicated’ or denormalized data which takes more memory.  Therefore, you may need to test both scenarios for your production system to see which is better.  However, the point I want to make here is that merely hiding tables from the client tools as I have done with the Product Category and the Product Sub Category tables does not really flatten the model into a star schema.  That is because the tables still physical exist and the calculated columns with the descriptions that I created in the Product table are merely pointers to the data in their original tables.  That is why you can only hide these tables from the client tools and not delete them.  If you truly want to flatten your model to a star schema, you will need to do this in a staging table back within a standard SQL Server database and then bring the resulting tables into the model.

C’ya next time when I look at KPIs in the Tabular model.