At a Loss

There is so much going on that probably deserves a rant tonight.  Everything from the weather to the lies coming from politicians on both sides making it difficult for people to decide what is best for not only this country but for the well being of the entire world.  But I just could not decide on what to focus on tonight.  Perhaps because I really cannot focus tonight.  In fact, I wasn’t going to post anything at all, but some of you who read what I post on a regular basis deserve at least something.

You see, my wife was diagnosed with colon cancer 3 years ago and it has been a real rollercoaster ride of emotions.  There have been times when she has been bad and times when she has felt rather good.  Tonight it is rather bad.  I don’t know what to do.  She had gone through chemo and had part of her colon removed two years ago, but they said that it had metastasized. They wanted to put her on chemo for the rest of her life which they could not promise much.  She refused to go back to chemo which has its own nasty side effects.  We’ve tried some other things and some indications have been that her condition was improving, but recently, within the last month, she has been getting severe stomach pains.  They want to do a colonoscopy today (Tuesday).  I’m afraid.  I’m afraid of losing my best friend of over 35 years.

So I’m going to end this post now with a simple request.  I ask that any readers who have been reading my blogs on a regular basis to pray to whatever God or Supreme Being you believe in for her health, to ease her pain and to help her live.  Pass this on to your friends so that they might pray for her too.  Her name is Susan.  Thank you.


Using the SSAS Tabular Model, Week 4 – Hierarchies

Over the last several weeks, I showed how to load a Tabular model in SSAS using not only data from SQL Server, but also from other data sources as long as you can create a link between each table and at least one other table.  Last time, I showed how to display the data in the model using Excel after creating a simple summation measure on the Sales Amount in the Contoso sales table.  In the resulting pivot table, I showed how to manually define a dimension hierarchy by dragging more than one field to the rows area of the Field list.  (Of course, I can also build a hierarchy of dimension values for the columns as well.)  However, I suggested that leaving it up to the end user to define the hierarchy may not always be best because it assumes that the user knows what the hierarchy should look like.  That may not always be the case.

A simple example might be a date hierarchy.   I might think that everyone would naturally use the hierarchy Year -> Quarter -> Month as shown in the following figure.

 That is not the only way to display sales data.  For example, I could be interested in how sales vary over the years within a specific month of a quarter as shown in the next figure.

On the other hand, defining the hierarchy Month -> Quarter -> Year, while technically yielding correct numbers, is not useful since the field Quarter serves no real purpose.

So how can I protect users from defining inappropriate hierarchies?  I could define the hierarchy as part of the model.  I can only create a hierarchy from fields within the same table.  If I want to create a hierarchy for Year -> Quarter -> Month, I can use fields already in the Date table.  Returning to SSAS and Model.bim tab, and display the model with the diagram view (remember the diagram view can be turned on using the button in the lower right of the Model.bim window.  Then locating the top level of my hierarchy, I right click on Calendar Year to display the following menu.

From this menu, I select Create Hierarchy.  This creates two rows at the bottom of the table.  The first labeled Hierarchy1 will be the name for the hierarchy.  Notice the different icon before this name compared to the other fields in the table designating it as a hierarchy.  The second row is indented indicating that it is a part of the hierarchy and shows the field CalendarYear as the first field in the hierarchy definition.

To add the next field to the hierarchy, CalendarQuarter, I locate and right click on the field name.  This time I choose Add to Hierarchy from the first dropdown menu to display a second fly out menu.  In this menu, I select the hierarchy to which I want to add the field.  Initially, I only have the one hierarchy so it seems obvious that I want to add the field to that hierarchy.  However, a table can have more than one hierarchy defined.  In those cases, the need to specify which hierarchy to add the field to become critical.

Similarly, I would add Month as the third field to the hierarchy.  By default, each field I add to the hierarchy is added to the bottom of the existing hierarchy.  So what if I accidently select the fields for the hierarchy In the wrong order?  Do I have to delete the hierarchy and start all over?  Fortunately that is not the case.  All I need do is right click on the field and use one of the options: Move Up or Move Down to change the order of the fields in the hierarchy.  Notice that I can also remove a field from the hierarchy if I added the wrong field.  I can even move a field to a different hierarchy (assuming I had more than one) if I dropped the field into the wrong hierarchy.

I might also want to change the name of the hierarchy since Hierarchy1 is not a very descriptive name.  By right clicking on the hierarchy name, I can choose Rename to give the hierarchy a better name such as YQM to indicate years -> quarters -> month.

Now I can use the Analyze in Excel option in the Model drop down menu to open another instance of Excel to build a pivot table.  However, if I want to continue working on the previous pivot table, I can open that instance of Excel and from the Analyze ribbon in the Pivottable Tools group, select Refresh All to update the PivotTable Fields list.  Then by replacing the individual dimension fields in my Rows area with the new YQM hierarchy definition, I will see a pivot table like the following pivot table in which I can click on the boxes before the year and quarter values to expand or collapse the hierarchy.

By right clicking on the value, I can use the Expand/Collapse option to display a fly out menu that allows me to expand or collapse all of the fields at that level among other options.  In summary, creating a hierarchy eliminates the need to decide which fields to include in the hierarchy and the order of those fields.

Next time, I will look at how to build the hierarchy when the fields needed are not all in the same table.

C’ya next time.

Freedom Of Religion, Not Freedom From Religion

The other day I was at Costco as we often do some of our weekly shopping there.  Of course they already have many of their Christmas items out as well as items for Hanukah.  It seems like each year the holiday items go on sale earlier and earlier.  With such a big emphasis on holiday sales boosting the bottom line of many retail establishments, I guess that should not be surprising.  However, that is not my point.

As I walking down one of the main aisles to the back of the store, I passed a nativity scene display.  Actually, there were two displays with a stack of boxes between them.  The figures were not quite life size, but nearly so.  The figure of Joseph held a cane but the cane was separate from the figure.  Anyway, as I was passing, I overheard two guys talking by the display.  The one said to the other, “I could use a cane like that.”  The other replied, “Don’t you think that you would go straight to Hell if you took it.”  The first answered, “So, I don’t believe in that.”

I continued to walk to the back of the store for the groceries I came for.  However, when I returned back up the aisle a few minutes later, I noticed as I passed the nativity scene displays, that both canes were missing with Joseph’s hand trying to circle around air.

Now I’m not sure those two were the two that took the canes.  I certainly did not see them anywhere in the store.  However, I have to wonder whether the person(s) who took the canes were so poor that they could not afford to buy a cane.  If so, I’m sure Jesus would not condemn them.  Or maybe the crime was just a crime of opportunity with no other significance than to just see if they could get away with it. (Note that does not make it right.)  However, if the person(s) who took the canes did it because they wanted to make a statement against the celebration of one religion’s holiday then they may have more to answer for at some point.  At the very least, just because someone does not respect someone else’s religion does not make it right to damage symbols of that religion, or as in this case to steal canes from a religious display.

The Founding Fathers were not all of one faith as some might have you believe.  In fact, there were several different faiths represented.  Remembering that many of the original colonists came to America from Europe to find religious freedom. (Check out the pilgrim’s reason for coming to America.  It was not just to have a big turkey dinner with the local natives once a year.)  In fact, the importance of freedom of religion was held in such high regard by the founding fathers that they emphasized its importance by making it the beginning of the first amendment as you can read for yourself here:

Congress shall make no law respecting an establishment of religion, or prohibiting the free exercise thereof; or abridging the freedom of speech, or of the press; or the right of the people peaceably to assemble, and to petition the Government for a redress of grievances.

It is amazing that they were able to express so clearly in a mere 45 words a set of beliefs while the new health care bill consists of several thousand pages that practically no one has read in its entirety much less understands. That is another issue.  Imposing one’s beliefs on another person no matter what the belief is the very definition of intolerance.  One cannot claim that others are intolerant while they themselves are intolerant.  When the government takes sides in such an argument, they are violating the Constitution by respecting the establishment of a religion.  If the government tries to eliminate all displays of religion, it is equally violating the Constitution by prohibiting the free exercise thereof.  Rather people should decide by their vote, political or economic, whether to support businesses or organizations that display one belief over another.  So going back to my story of the canes, if the people who took the canes really despise Christianity and its symbols, they should support stores that support their own beliefs, not steal or damage merchandise from stores that sell merchandise that offends them.

C’ya next time.

Using the SSAS Tabular Model, Week 3

So last time we finished building a basic model with data from a variety of different data sources.  Perhaps you are getting a bit impatient to see what the cube might look like.  If we had built this model in PowerPivot for Excel, we could at this point simply click the button from the PowerPivot window to create a Pivot Table or a Pivot Chart.  With a few additional clicks, we could drag a field or two from the fact table to the body of the table and then drag a couple of fields from the dimension tables to the rows and columns of our table.  Before you know it, we would have a useful analysis of the data in the form of either a table or a chart.

In SSAS, there is not direct way to show the pivot table and certainly, there is no pivot chart.  However, you  have noticed an option in the Model dropdown menu: Analyze in Excel.

The first time I tried this, it looked promising so I clicked it.  It did open Excel and it did show the Pivot Table Fields panel along the right side of the screen.  However, every field that I tried to drag from the Sales table, such as SalesAmount or SalesQuantity, to the Values portion of the fields panel gave me the following error message:

I was confused by the message.  After all, why would I not be able to move a numeric field (yes, it is formatted as currency, but that still makes it numeric) to the Values portion of the table.   To be honest, I puzzled over this message for some time.  The conclusion I came to is that either this is an error or perhaps, just maybe, it is because the data for this pivot table does not really reside in Excel.  This is a fact that you can easily verify by opening the PowerPivot window only to discover that there is no model defined within Excel.  SSAS is merely using Excel as a vehicle to display the data from the Tabular model.

If that is true, then perhaps the solution would be to create a calculated measure back in the Tabular model and see if that field can be placed in the values portion of the pivot table.

So I returned to the Tabular model in SSAS and looked through the menu option until I found the option to Show Measure Grid in the Table drop down menu.

I now had a calculation area beneath each table similar to the one found in PowerPivot 2012 for Excel that I have discussed before.  Clicking in one of the cells beneath the Sales Amount column, I entered the following DAX equation:


Crossing my fingers, I pressed the Enter key and was rewarded after a second or two with a calculated value in the cell that look like:

SumOfSales: 8341224364.8324

That value looked like it could be the sum of sales for all products across all years for the Contoso database.  However, reading large values such as this can be a challenge.  My first thought was to right click on the value to see if there was a format option in the menu.  There wasn’t.  However, I noticed that the Properties window displayed my new calculated measure along with some properties.  One of the properties, Format, looked promising so I clicked first in the field and when a dropdown arrow appeared to the right of the field, I opened the dropdown to show the built-in formats available to me.

Selecting Currency gave me a nicely formatted value of:

SumOfSales: $8,341,224,364.83

Great.  So now that I have at least one calculated measure, I thought that I might try that Analyze in Excel option again. This time a new instance of Excel opened with the Pivot Table Fields populated at the top with a ‘new’ table containing one field, SumOfSales, my calculated measure.  I could easily tell that this table was different from the others because it had a summation symbol before the table name letting me know that this calculated value came from the Sales table.

This field I could drag down to the Values box.  I could also simply click the check box before the calculated measure’s name and it would automatically be sent to the Values box.

I then added the Product Category Description, Product SubCategory Description, and Product Description in that order to the Rows box.  I also added Calendar Year to the Columns box.  I now had something that closely resembled a pivot table that I might have created using PowerPivot for Excel.

The problem with dragging the three individual description fields over to the Rows box is that it would be so easy to get the order of the descriptions wrong.  For example, I might have:

Product Description
Product SubCategory Description
Product Category Description

Or I might even have:

Product SubCategory Description
Product Category Description
Product Description

The point is that unless user really know the structure of the data, it is very easy for them to get the hierarchy of a set of dimensions incorrect.  I decided that fixing this problem would be my next task that unfortunately for you will have to wait until next week.

BTW, my presentation on Tabular modeling at OPASS was postpone for one week due to scheduling conflicts by the meeting venue.  The meeting was rescheduled for October 24th.  If you are interested, go to to sign up.


What If Race Really Didn’t Matter?

I don’t know about you, but it sure seems that the more politicians try to claim that race doesn’t matter, the more it seems to matter.  Recently the state of Florida issued some new achievement goals as part of their new five-year plan.  Rather than stating some overall achievement goal, the State Board of Education chose to tie the achievement goals to race.  Here are the numbers.

By 2018, the State Board of Education wants 90% of Asian students, 88% of white students, 81% of Hispanic students and 74% of black students to be reading on grade level.  For math, the numbers are 92%, 86%, 80% and 74% respectively.  The first question should be, “Where are we today?”  For reading, the numbers are 76% for Asian, 69% for White, 53% for Hispanic and 38% for blacks.  For math, the numbers are 82% for Asian, 68% for White, 55% for Hispanic and 40% for black.  The second question might be, “How much improvement is needed?”  The following table shows the increase in percentage for each racial group.

Racial Group





% increase

















































It is the last column of this table that you really want to look at.  This column shows the percent increase in the number of students in each racial and test area that must be achieved.  Unless I am missing something, there are very big expectations for some races and relatively little expectations for others. 

In any case, I first want to say that it would be ideal if when you looked at race, that achievement scores were comparable across the board.  But the fact of the matter is, they are not.  Simply defining a performance goal to narrow the gap between the best performing races and the worst performing races may not be the entire answer.  It implies that schools and teachers will need to spend more time with some racial groups to meet these goals while ‘ignoring’ the other groups.  As much as some people might say that will not happen, the fact remains that even teachers only have 24 hours in a day and of that, they only have the students for about a third of that time.

The Florida Education Commissioner, Pamela Stewart, has been quoted in the Miami Herald as saying that “Florida believes every child can learn.”  No doubt.  However, I think we need to ask the following questions.  Does every child want to learn?  Is every child motivated by their home, friends, and family to learn?  Does every child have a goal for what they want to do in life?  Or do they just go through the motions, day after day, school year after school year hoping things will eventually all work out.  Hope is not a plan (Sorry Mr. President).  I submit that if the answer to either of these questions is no, perhaps a better challenge for society is to determine why some children may not want to learn and are not motivated to learn.  Without solving this fundamental problem, progress may be limited. 

Case in point.  I know of a student a few years back who did not perform to grade level expectations in high school.  Suddenly, this student started doing much better in his studies.  When asked what changed, his response (paraphrased) was that he had to do better in school now so he could get a good job when he graduated in a few months so he could support his new baby boy.  Ok, perhaps not the best motivation, but at least it was motivation that turned him around to be a contributor to society rather than a drain on society. 

I know of another student who when asked why she did not try to do better in school replied that all she wanted to do when she got out of school was to work with her Mom being a hair dresser and all that study ‘stuff’ would not mean a thing anyway.

Another student was just biding time until graduation until he could join his father painting houses.  When shown how knowing basic geometry could help him calculate how much paint to buy to paint a house, he suddenly became interested in doing geometry problems.

Motivation!  Nothing drives people to success more than this one factor.  Do some racial groups motivate their children more than others?  Perhaps.  If that is a real factor, how can we provide more motivation to all students?  How can we bring successful people from different racial groups into schools to motivate students to find the success that is within them just looking for a way to express itself?  How can we increase the motivation that must also come from home, family and friends?  Maybe schools could consider evening or weekend seminars for parents on, ‘How to Motivate your Child to Success’.  Maybe there are other ways that you can come up with.  Your local schools are waiting to hear from you.

Of course, as in all of my Tuesday rants, this is just my opinion and not the opinion of my employer or anyone else.  Fundamentally, I applaud the ultimate goal of the state to close the gap between the percentage of students scoring at or above their grade level by all students regardless of race.  But by making race the central point of these goals, it has done more to cause dissension between the races than to bring the races together in unity.  

What if race didn’t matter?  What if we looked at raising student achievement by motivating students to find ways that they can make a difference in our world without regard to race?  What if we could motivate each and every student to work hard for their own success?  What a different county this could be.

C’ya next time.

Using the SSAS Tabular Model, Week 2

Last week I began creating a model in SQL Server Analysis Services (SSAS) using the tabular model.  It pulled data from a SQL Server database named Contoso 2012 which is available from CodePlex.  Despite the fact that the SQL database consists of several tables, it does not have all the database tables I need for my analysis.  So this week I will pull data from additional data sources into the model and then link the data together.

To begin, I am going to click again on Import From Data Source found in the Model pull-down menu.  This time however, I am going to scroll down to select Microsoft Access from the Table Import Wizard dialog as shown in the following figure.

I will need to use the Open dialog to navigate to my access database and select it.

In the following screen, the wizard asks me for the specific windows user name and password that SSAS needs to use to connect to the data.  In this case, I can use my regular user name and password.  However, the dialog also lets me use a service account which might be preferred in a production environment.

After connecting to the Access database, the wizard gives me the option to either select data from Access by table or by creating a custom query.  This is starting to look very similar to PowerPivot.  Assuming that I choose to load data by selecting tables, I next see a dialog that lets me select the table(s) I want, rename the tables with a user-friendly name, select columns to retrieve, and define a filter on the rows using one or more of the columns.  The following figure shows that my Access database only contains a single but important table; ProductCategory.

Next I need information about the stores where the sales occur.  This data happens to be in an Excel file.  By returning to the Import From Data Source menu option, I can select Excel File to read an Excel spreadsheet to retrieve this data.

Again I will need to use the Open dialog to navigate to the folder where my Excel file exists and select it. An important addition option in this dialog lets me define whether the Excel sheet uses the first row to hold the names of the columns.  If your spreadsheet does not have column names in the first row, the wizard still loads the data, but it generates column names using default values with an incremental number suffix for each column.  Since you would probably want to change these column names anyway, I strongly recommend adding the column names in the Excel spreadsheet first if they do not already exist.

The rest of the process of loading the Excel data is similar to loading data from Access or SQL.  I must provide impersonation information to SSAS to read the data and select which worksheets from the Excel file I want to load (each sheet is treated as a Source Table).  Interestingly, I can even provide a ‘Friendly Name’ for each table and I can filter the data retrieved on the columns and rows available, just like SQL and Access tables.  The last step of the wizard shows the status of the import including the number of rows actually retrieved.

Before I can continue building a cube, I need to create relations between the tables if they do not already exist.  If I pull data from a single SQL Server database, I may already have relationships defined between the tables if I took the time earlier to define those relationships in SQL Server.  The Import Wizard sees those relationships and maintains them in the model as it imports the data. However, if relationships do not exist between the tables in my SQL database or if I am pulling data from multiple data sources where relations obviously would not exist ahead of time, I must define the relationships needed.

In the Tabular model as with PowerPivot itself, relations can only be defined between individual fields in each of the tables of the relationship.  I cannot have multi-value fields in the relationship.  The key to solve this problem is to create calculated columns to combine the information from each of the two or more fields that define the unique row criteria for the table.  Then I can define the relationship between the tables using these calculated columns.

To create my relationships, I generally start from my fact table and look for the foreign key columns.  I first select one of the foreign keys and then choose Create Relationships from the Table dropdown menu as shown here.

Because I already preselect a column from my fact table, the Create Relationship dialog pre-fills the Table and Column fields in the next page of the dialog.  I can then select the Related Lookup Table from the dropdown list along with the Related Lookup Column.  In the following figure, I began from my fact table: Sales (My user friendly name for the FactSales table in Contoso) by selecting the DateKey field.

Note: Sometimes merely selecting the Related Lookup Table value allows the wizard to ‘guess’ at the Related Lookup Column.  However, if the wizard cannot ‘guess’ the name of the lookup column, I may need to select it from the corresponding dropdown menu.

After defining all the relations, or at least all the relations I think I have, a good way to check that every table has been related to at least one other table is to switch to the diagram view of the tables in the Model tab.  To do this, locate the two buttons in the lower right corner of the Model.bim page.  The diagram view is the button that looks like a small three-table diagram.

This view allows you to see all tables, columns, KPIs, measures, and hierarchies defined in the model.  As I will show in future weeks, I can even edit much of my model using this view instead of the table view.  Note, I can also adjust the zoom of the page to show all of the tables as in the above figure, or I can zoom into a small group of tables or even a single table to work on that table.

Tables that do not have a connecting line to at least one other table such as the Entity table in the previous figure will be obvious.  I can then either return to the tabular view and the Create Relationship dialog I used above, or I can click on the field from the child table (often the fact table) and ‘drag’ the linking field to the corresponding field in the lookup table.  In the following figure, I am creating a link between the StoreKey field in the Sales table and the StoreKey field in the Stores table.

Ultimately you want relations connecting all tables as shown in the following figure.

You can also go to the Manage Relationships… menu option in the Table dropdown to review the relations between the model tables.

Next time, I’ll show how to display the model as a pivot table (cube), and then we will continue with ways in which we can refine the model.

BTW, anyone in Orlando next Thursday, October 18th is invited to stop by the OPASS meeting where I’ll be speaking on this very topic.  (


Know Your Customer

I’ve been creating computer software for a few years now, enough to know that one of the toughest lessons to learn when designing software is that you should not design the software as if it were for your own use.  Rather, you need to try to put yourself in the shoes of your customer (figuratively of course) to understand what they really want to get out of the application and how they expect to interact with it.  Actually, let me expand that rule to any product or service, not just software.

What do I mean by that?  Well recently I heard a story about the Chevy Volt.  Perhaps you know about this car.  It is an all electric car, not a hybrid.  According to the story, it is not selling as well as folks in Detroit expected.  Sure it is economical.  Sure it is environmentally conscious.  Sure the President wants everyone to buy one.  However, sales have been disappointing.

Some people believe that the reason many people will not consider the Volt is because they fear being stranded somewhere without a place to plug the car in to recharge its batteries.  Without a small gasoline engine to take over when the batteries are down, that certainly could be a problem.  But even if you just drove the Volt to and from work in well populated areas over relatively short distances no more than 25-30 miles in each direction, would you feel comfortable?  What if you forgot to plug in the car the night before because you got home late and were so tired that you simply forgot?  How do you tell your boss that you will not be coming into work that day because your car needs to charge?  (A good argument for working from home.)  Or maybe you just cannot pull the car into your garage to plug it into the recharge station because your garage is just too full of junk?  (Come on, you’ve seen those garages when their doors are open that are so jam packed with ‘stuff’ (a synonym for trash) that people have to squeeze through a narrow tunnel of boxes to get to the door into the house.)

So while the intentions of the designers may have been great, they forgot to consider the human factors in owning and operating an electric car that make it different from a standard gasoline car or even a hybrid.  Maybe eventually fast recharge stations may be more available, perhaps even as additions to regular gasoline stations just like diesel pumps were added to many stations years ago.  Maybe parking lots and parking garages will include recharging stations that will just add the cost of recharging to the cost of parking.  But that’s the future.  Today the number of people willing to depend on an all electric vehicle as they primary vehicle is very small.

Let’s go back to software.  When you get a new project, what is your first inclination?  Is it to rush back to your computer and begin coding the application?  And if so, do you continue to code day and night Monday through Sunday until you finish the application and then go to the customer to proudly show off your wonderful creation?  Does the customer echo your praises telling you what a brilliant programmer you are?  Probably not, and why not?

You designed a perfectly good application based on what you thought you heard using capabilities, features and tools you are comfortable with but those may not be the same things that the customer thought they were asking for when you first met.

What went wrong?  Sometimes it takes more than one meeting to get to really know your customer and to really understand what they need especially when they have not articulated their desires clearly.  The way I suggest approaching this dilemma is by iterative design.

At the first meeting, try to identify what the absolute needs of the customer are and what are features that they would just like to have.  Ask about each feature and function and how it serves to meet a specific need.  Take the time to really know the customer’s pain.

Then go back to your desk and design a prototype system.  I’ve designed prototype systems on paper, white boards, Visio, and even using Visual Basic to create simple forms and reports without any really code behind them.  Remember that they are just visual tools that you can take back to the customer in a week or two (or longer if the project is really large and complex).  When you meet with the customer the second time, go through the design explaining how users would interact with it going from screen to screen following the path of the data from entry through processing and finally to storage or reporting.  Encourage the customer to ask questions.  But most importantly, don’t be afraid to modify your design either on the spot (as with a Visio, white board, or Visual Basic model of the system) or to go back and create a new paper model based on your improved understanding of what the customer wants.

While you may be able to nail down the design in one shot, don’t be afraid to evolve the design over a series of meetings.  Changing design at this point is relatively easy and inexpensive compared to changing the design after you spend countless hours coding an application to work a specific way.

Even after you get a design that the customer agrees to (and get them to actually sign off on the design), you should periodically take portions of your application back to the client for a quick ‘show and tell’ session to let them see your intermediate progress.  Not only do these sessions make sure that the design stays on track for the customer’s needs, you will build a lot of goodwill with the customer because they will see you as actively partnering with them in making the project a success.

Well, that’s just some thoughts on design methodology for this week.  Hopefully it will help you with your next application design project,

C’ya next time.

Using The SSAS Tabular Model, Week 1

Server Analysis Services 2012.  With the introduction of this release of Analysis Services, Microsoft provides Business Intelligence developers with a choice of two models.  First there is the traditional Multi-Dimensional model that has been part of Analysis Services since the beginning.  In fact, other than a few minor tweaks here and there, the model is pretty much the same as the version of Analysis Services in SQL Server 2008 and 2008 R2.  On the other hand, the second model, the Tabular model, is new to Analysis Services.  However this model is not really new to anyone who has been using PowerPivot for Excel version 1 that became available roughly with the release of SQL Server 2008 R2.  Even closer in design and function is the latest version of PowerPivot 2012 for Microsoft Excel 2010 or the built-in version of PowerPivot in Microsoft Excel 2013.

To follow along with this blog and the ones that follow over the next several weeks, you will need to have a copy of SQL Server 2012 installed.  I’m currently running the BI edition of SQL Server.  However, the Enterprise version will work as well.  When I switch over to Excel to display the resulting pivot table, I will be using Excel 2013.

To begin, open the SQL Server Data Tools.  This program is simply the renamed version of Business Intelligence Development Studio (BIDS) that you used in 2008 and 2008 R2.  In the initial screen (Start Page), select New Project.  This opens the New Project dialog shown in the following figure.

From the Installed Templates, open the Business Intelligence group if it is not already opened by clicking the right pointing arrow before the group name.  Then select Analysis Services.  From the available templates in the Analysis Services area, select Analysis Services Tabular Project.

With the template selected, provide a name for the project.  The default location for the project will be in your documents folder under Visual Studio 2010\projects. (No I do not know why it still defaults to Visual Studio 2010.)  You can also enter a separate Solution Name although I usually allow the solution name to default to the Project name.  You can also decide whether to create a new directory for the solution and I usually allow that to happen.

After clicking OK, Visual Studio creates a new project.  However, that project is empty and at first you may be confused as to what to do since there are no tools in the toolbar.  You need to select the Model tab from the top of the page as shown in the following figure.

Click the first item in the dropdown menu, Import from Data Source.  This opens the following dialog that allows you to select the type of data connection you want to use for your next data set.  Just like PowerPivot for Excel, the Tabular model allows you to connect to a wide variety of data sources.  Furthermore, you can add data tables from different data souces into a single model as long as you can create links between the tables.

I’m going to begin my model with data from the Contoso 2012 sample data.  Therefore, I select Microsoft SQL Server as my data type.  As with any connection definition, the Table Import Wizard begins by prompting for the server name which in my example will be localhost.  You then have the option of either logging into the server with your current Windows Authentication or with a SQL Server Authentication.  Finally, you must select the database from which you want to get your data.

On the next screen you can specify an impersonation account that Analysis Services can use to access the data.  This can be a Windows Authenticated account or preferably for production systems, this should be a Service Account.

With this information Analysis Services can how access the database.  The wizard continues in the next screen by asking whether you want to load data by tables from the selected database or whether you would rather write a custom query to retrieve only the data you want.

Your initial thought might be that you would have more control over the data that you bring into your model if you write your own query.  Well, just like in PowerPivot, there is more than one way to insure that you only bring into the model only the data needed.  Therefore, I generally prefer to select the data for my models by selecting the tables from the database.  In fact, if I choose the option to pick my data from the tables and views in the database, the wizard shows me the following dialog.

This page should look familiar for those of you who have already been working with PowerPivot.  You can select individual tables and views from the list displayed by clicking in the checkbox before each Source Table name.  You could also identify a fact table and select it and then click the Select Related Tables to let the wizard find the immediate tables linked to the table you selected.  What do I mean by the immediate tables?  The wizard only identifies the first generation of tables linked to the starting table.  If the data model has second, third, or later generations, you need to step though each generation of tables to find the corresponding related tables.

You also have the option of defining a user friendly name for each table.  After all, your end users probably don’t need to know that you precede the names of dimension tables with ‘dim’ and fact tables with ‘fact’.  You can also add spaces between words of multi-word table names, but I do not recommend that.  It just leads to the requirement of placing table names within quotes when you later write DAX formulas referencing columns from that table.

By selecting a table row, you can also filter the data in that table by rows or by columns.  After selecting a table and clicking on the button Preview & Filter, the wizard displays the following dialog page which includes a sample of the data in the table.

You can easily remove columns that you do not need such as the ProductSubCategoryDescription column in the above figure which appears to contain the same data as the ProductSubCategoryName column.  To remove a column, just click the checkbox to the left of the column name to remove the checkmark.  When you remove a column at this point, the data import routine will not bring that column into the Analysis Services model saving space and improving performance.  Always limit the data you bring into the model to those columns that you and your users need.

If you click the dropdown button to the right of the column name, you will see a menu like the following:

This menu allows you to change the sort order of the column, but more importantly, it allows you to filter the data by specific values in the current column.  For example, in the above figure, I am limiting the records returned to only those with a ProductCategoryKey equal to ‘1’, ‘2’, ‘3’, or ‘4’.

Using both of these methods to minimize the data copied into your model, you want to analyze each of the tables you include in the model.  When you have carefully considered the columns and rows to be included with each table, click the Finish button at the bottom of the Import Table Wizard screen.  The wizard then proceeds to process your request by gathering the requested data from the database.  The following screen shows you the progress of the data import.

Do not interrupt the import process until all of the tables have a Status of Success.  Notice that the Status message also includes a count of the number of rows of data imported from each table.  When all of the data has been imported click the Close button at the bottom of this dialog page.

The Model tab set, previously empty, now shows one tab for each table imported.  These table tabs appear at the bottom of the page.  Notice the similarities of this screen to the PowerPivot window I showed you previously in PowerPivot 2012.

I’m going to stop here for this week.  Next week, I will show you how to pull additional data from other data sources to build a multi-source model.  To save your work, go to the File menu of Analysis Services in the upper right corner and from the dropdown menu, select Save All.  Then you can close Analysis Services until next time.

BTW, next Saturday, October 13th, I’ll be at SharePoint Saturday in Tampa, FL presenting a topic on how to use the Chart web part with different data sources.  If you are in the area, stop by to say, ‘Hi.’  To find out more about SharePoint Saturday, go to


Software Trends and Politics

I was thinking the other day about the trend in software today to move more and more things to the cloud.  Of course, there are many different definitions about what that means.  Here are just a few of the ones I have been able to find:

IaaS – Infrastructure as a Service – This model provides physical and/or virtual machines for companies to use by subscription/rent rather than having those companies purchase the equipment themselves.  Supposedly, this will save money because you only pay for the hardware you actually need and these large companies can negotiate better deals with the hardware manufactures due to the quantities of equipment they buy.  In addition, they will buy the biggest hardware they can get and virtualize it across multiple users.

PaaS – Platform as a Service – This model not only provides hardware (or they ‘buy’ it from IaaS vendors), but they also provide the operating system, computer languages, databases, etc.  Again, by building large systems with many users, these companies can negotiate better prices from software vendors as well as better prices on hardware than individual small companies can get.

SaaS – Software as a Service – This model extends the PaaS model by providing not just the supporting operating systems, languages, and databases, but the applications that the user needs to run.

STaaS – Storage as a Service – This model provides ‘rental’ space for storing data, files, and applications.  Some common STaaS companies you probably already deal with include Box, SkyDrive, and others.

DaaS – Data as a Service – This model provides libraries of data that you can use to ‘discover’ relations in your data.  Some of the libraries might include basic demographic data, census data, sales data, etc.  Some of these services have been around for a long time providing data to companies who may want to perform analysis on their company’s activities.  For example, back in the early 90’s I worked briefly for a company that contracted with manufacturers to perform marketing analysis on advertising promotions.  We would purchase sales data from various retail chains and compare actual sales against manufacturing shipments to determine how effective sales promotions were.

There are others, but the point is that I started my IT journey working with computers back in the day when the mainframe was king and personal computers were just coming out and considered by nearly all IT professionals as nothing more than toys.  In the 80’s and 90’s personal computers in business led first by IBM and then by the Compaqs, HPs, Dells, and others virtually killed the market for ‘big iron’ computing.  This freed computing resources from the shackles of centralized control giving every department the freedom to use computing power to create that applications they had been asking for.  Soon everyone was creating their own spreadsheets to analyze data and Access databases proliferated. The point was to decentralize computing and to get away from the ‘controlling’ environment of the centralized IT department that determined what you could have and when you could get it.  Centralized software as considered ‘evil’ and decentralized software represented ‘freedom’.

Over the years, concessions were made to client-server modeling computing largely due to the need for a centralized database that everyone could access rather than dozens or even hundreds of small data files that were hard to aggregate and get a big picture of what was really going on.  I won’t even go into the issues of Access databases being passed around with everyone making their own little change here and change there resulting in dozens of inconsistent versions.

Soon applications followed data to the servers again largely to provide consistency, a single place to update new releases, and because server hardware allowed for more powerful CPUs, more memory, and larger disk drives.  There was also the realization that most of the ‘freelance’ software developers were not creating enterprise quality applications resulting in a lot of loss productivity when these applications failed and the original developer had moved on to their next victim….a….company.

These steps led in time to a consolidation of processing power that has not stopped, but has been accelerating and is now headed directly toward the cloud.  Cloud vendors make all sorts of promises telling you how their cloud products will save you money, result in less support needs by your company, and make updated software (no matter whether you are talking about the OS or an application) available incrementally rather than with massive version updates.  (Maybe another time I look at the challenge incremental updates might pose to companies testing and accepting changes that get pushed out monthly or even weekly.)

Isn’t this all similar to politics?  One political party wants to centralize more power in the government and have people more dependent on what the government can provide to them rather than what the people can provide for themselves.  The other political party wants to minimize the government and let the individual (or company) do what they feel is best and what works best in a ‘free’ market.  Isn’t this the same trend we see over time in the computer industry?  Furthermore, are these trends synchronous?  And if they are synchronous, is one causative of the other or are they merely associative?

I suppose that is something to ponder as we approach another major election.  My only recommendation is that if you are not registered, register, study what the candidates stand for and then get out there and vote based on your beliefs.  It may not change the direction of computing going to cloud, but it could change the direction of the country.

C’ya next time.