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. (http://orlando.sqlpass.org/)