Choosing tables, columns and rows to import
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.
Choosing tables
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.
Choosing rows for each selected table
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.
Choosing which columns you want to import
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. |
Processing your tables (importing their data)
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!
