557 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 five 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!
Let's suppose that we want to be able to create a pivot table just showing total sales by region and species:
A simple pivot table, with a very simple data model!
To do this, we need to hide some tables completely, and hide certain columns from others.
Although you can right-click on tables in grid view to hide them, it's easier to do this in diagram view. Select the first table you want to hide:
You don't want to see this table in your pivot table field list, so select it.
Now hold down the CTRL key and select the other tables you want to hide (you don't have to do them all in one go, however):
Here we've selected all of the tables shown boxed.
Right-click on any one of the tables, and choose this menu option:
Choose to hide the selected tables from client view (ie from any pivot table).
SSAS Tabular dims the tables you've hidden:
The tables which are hidden are dimmed a bit.
Here are the ones we haven't hidden, and why:
|Table||Why we've kept it|
|Region||Because we want to aggregate by region name.|
|Species||Because we want to aggregate by species name.|
|Transaction||Because we want to create measures summing quantity and averaging price, and show these in our pivot table.|
If you were to create a pivot table based on this model now, you'd only see 3 tables:
The only tables you'd now see in a pivot table field list for this model.
Having hidden tables, it's time to hide specific columns:
For each table, select the first column you want to hide then hold down the CTRL key and select subsequent columns.
You can then right-click on one of the selected columns to hide them all:
Right-click to hide the two ids, which no user will be interested in.
Note that although we can hide the id numbers, we can't delete them, since then there would be nothing to link the tables together.
For the transactions table, you'd probably hide all of its columns:
We'll have to create measures later to allow us to show the total quantity sold or average price for any region/species combination, but we should hide these base columns.
Here's what our final model looks like:
There's not much left!
And here's what a pivot table using it will look like:
There's nothing here to confuse even the simplest of users!
The next step in the process is to create the measures we want to show in the pivot table.
|Parts of this blog|
|When:||20 Nov 21 at 15:04|
Do you guys know if there is anyway of hiding from client tools programtically with VBA? Whilst using the data model is not new to me as an early adopter of PQ & PP, I am working on my 1st project in which i am generating all of the queries, relationships and measures programatically with VBA which is working amazingly, however not being able to hide from client tools has become a sticking point!
PS: have you looked at the Lamda function yet, it may make a good video, especially when you get into recursive Lamda!
|When:||22 Nov 21 at 09:39|
Full disclosure: I didn't know you COULD generate the data model using VBA! Do you create a reference in VBA to another library, and if so which? As you will have gathered from this, I don't know how to hide things from the data model in VBA. It may well be that this is part of the SSAS client interface, and so included in a different object model. Maybe!
As to lambda functions, as far as I know these are not included in DAX, although they are now possible in Excel.
|When:||22 Nov 21 at 12:27|
You can use the macro VBA macro recorder to record the steps in PowerQuery and it will basically generate the text string for the PQ advanced editor. This then allows you to use VBA to close and load to data model. Once all the tables are loaded you can use VBA to write the DAX measures and relationships.
Must confess I would not of got this far without the help of video 51.5 in your pivot tables playlist!
You do not need to add any additional reference libraries.
In terms of LAMBDA I was referring to the Excel function.
25 Aytoun Street