564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
How to create a data model in SSAS tabular
Part three of a seven-part series of blogs
The first - and most important - part of using Analysis Services (tabular mode) is to create a data model, incuding only those tables and columns you need. This lengthy blog shows you how to go about creating the perfect data model!
This blog page shows how to choose which tables, which columns and which rows you want to import.
As a general principle, you should import into your model only those tables, rows and columns that you actually need for reporting. The rest of this blog explains what you might and might not need to import.
A good place to start for our database is with the transactions table, which contains details of who bought which products:
Tick the first table that you want to import, and give it a friendly name (users of your data model don't need to know that the table is actually called tblTransaction).
In fact, Transaction is a reserved word in SSAS Tabular, and is not an ideal friendly name to use, as you'll find you always have to include it in inverted commas in expressions. C'est la vie.
To save time, you could now choose to include related tables:
If you click on this button, SSAS tabular will include those tables immediately connected to the one you have selected.
Here's what you'd get if you clicked on the Select Related Tables button once with the Transaction table selected:
The tblPos and tblProduct tables are directly connected to the tblTransaction table.
Here's why this happens:
The database diagram in SQL Server shows that there is a direct relationship between the tblTransaction table and other tables.
The tables you should select to follow this tutorial are as follows:
Rather tediously, we've removed the tbl prefix from every one of these 10 ticked tables to give them friendlier names.
Having chosen tables for the model, you could now filter the rows included. For this example, suppose we only want to include transactions where at least 2 items were bought at a time.
To filter rows for a table, select the table then click on this button:
With (for example) the transactions table selected, click on this button to filter its rows.
Click on the drop arrow to the right of any column that you want to filter by, and choose your filter:
Here we're going to show only the transactions where the quantity is more than 1.
You can now complete the dialog box which appears (it's pretty obvious what to do):
Choose to show transactions where the quantity is more than 1.
You can tell you've applied filters to a table by the link which appears:
Clearly we're not going to import all of the transactions.
When you click on this link you can see what filters you've applied:
SSAS tabular shows a list of filters applied to the selected table.
If you set filters for a table, the excluded rows won't be imported into your data model, and won't therefore be available for further analysis. Then again, they won't clog up your workspace server either.
You can usually reduce the size of your data model by excluding some of a table's columns. Because of the way SSAS tabular stores data, it is particularly important to exclude columns with a high level of granularity (ie with lots of different values). What this means is that the TransactionId column will take up lots of space:
There are 4,234 transactions where someone bought 2 or more items at a time, and each has a separate id number. This will take up a lot more space than a column which contains lots of duplicate values (such as the price or the quantity columns).
You can choose to exclude columns by clicking on the same Preview & Filter button as you used for excluding rows:
Select the table whose columns you want to choose or exclude, then click on this button.
You can now widen the columns to see the names properly:
Use this double-headed arrow to widen each column.
Untick the columns you want to exclude, using the table below as a guide:
You can use the tick box at the top left of a table to include/exclude all columns simultaneously.
For the above table, here's what you should include/exclude:
|Column||Include or exclude?|
|TransactionId||Untick (exclude), as you're unlikely to want to sum, count or perform any other aggregation based on the unique transaction numbers!|
|PosId, ProductId||Include, because even though you don't want to be able to aggregate these numbers, they are the only way for each transaction that you can find out which point-of-sale and product record it is to do with.|
|Price, Quantity||Include, because you've very likely to want to create measures (calculations) showing things like average price or total quantity sold.|
As another example, here's what you'd include in the tblAnimal table:
See below for why!
And here's notes on each column:
|Column||Include or exclude?|
|ProductId||Include, because this links to the transaction to tell us what each person bought.|
|ProductName||Include, because we may at some point want to show (for example) total sales by product.|
|AnimalId||Include, because it links to the animal, and hence the species.|
|FullPrice||Exclude, unless we think that we may at some point want to aggregate this field.|
|TargetGenderId||Exclude, because we're not including the tblTargetGender table in our model and hence won't want or be able to report by target gender.|
|AgeRangeId||Exclude, because we're not including the tblAgeRange table in our model, and so won't want or be able to report by target age range.|
When you've finished and choose to go on to the final stage of the wizard, SSAS tabular should import the rows/columns you've chosen:
You should see something like this. Although there are over 13,000 transactions in the underlying database, only 4,234 have been imported because of the row filter we set.
You can now tidy up your data model, as described in the next part of this tutorial!
|Parts of this blog|
|When:||25 May 17 at 09:05|
I am new to SSAS. I wanted to know is it necessary to import the rows while creating the data model. Is there no way of creating the data model with 0 rows, deploy it and then I refresh the table?
|When:||25 May 17 at 16:35|
I would have thought that made perfect sense. You could then process the table when you've got the model set up. Perhaps you could let me know via this forum whether you ran into any problems with this approach?
25 Aytoun Street