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 13^th, 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 http://www.sharepointsaturday.com.

C’ya.