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 seven 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 page shows how you can add a table or a table column to your model.
Deleting a table or model is easier: you just right-click on it and choose Delete.
Suppose that you decide you want to include the centre type table in your model. To add a table it doesn't make any sense to go through the rigmarole of creating a new connection to your database; instead, you can use your existing connection:
Choose this menu option to use an existing database.
Note that you can also use this tool to do the same thing:
Click on this tool to choose to use an existing database connection.
You can then double-click on the connection you want to use:
It's just as well we gave the connection a friendly name of MAM when we created it; you can now double-click on the connection to open it.
After (possibly) entering your password, you can now choose to add a table to your model:
Tick the table(s) you want to add, and give them friendly names as shown here.
When you finish the wizard, SSAS tabular will import all of the rows in these tables:
Here we've only chosen one table to add.
The new table appears in the data model:
The problem now is that we need to add the CentreTypeId column to the Centre table!
To change a table, you should be able to right-click on it and choose its properties:
Memo to Microsoft: where is the Properties menu?
However, since you can't do this, choose this menu option instead:
Make sure you've selected the table you want to amend in some way, then choose this menu option.
You can now make any changes you like:
Here you should tick the CentreTypeId column to include this in the model.
SSAS tabular adds the extra column to the model, which you can then drag onto the corresponding field in the CentreType table to create a relationship:
Linking each shopping centre to its type.
If you import two tables together, SSAS tabular will also import any relationship between then in the underlying database. If, however, you import the tables in two separate chunks (as we've done above), you'll have to create the relationship manually.
You could now include the CentreTypeName column in your pivot table, to show total quantity sold by type of centre:
Total quantity sold summed by centre type and species.
And with that, I'll end this long blog on creating a data model in Analysis Services (tabular model)! Note that in the UK we also run classroom courses in SSAS.
|Parts of this blog|
25 Aytoun Street