Tabular SSAS Models in Profitbase Studio

! The tabular model in PS6 was changed from build no. 5573 / 24.SEP.2015. This document is based on new version.

Tabular Models

Tabular models are in-memory databases in Microsoft Analysis Services (SSAS).  It is a columnar database capable of high performance and compression ratio. Different fact and dimension tables can be set up in a star/snowflake model so that the clients (e.g. Microsoft Excel and Microsoft Power View) can access them fast. DAX is used for scripting which is similar to using excel formulas.

Analysis Services tabular models support most of the same query languages that are provided for access to multidimensional models. The exception is tabular models that have been deployed in DirectQuery mode, which do not retrieve data from an Analysis Services data store, but retrieve data directly from a SQL Server data source. You cannot query these models using MDX, but must use a client that supports conversion of DAX expressions to Transact-SQL statements.

It is not a good idea to use the tabular model when sources are based on dimensional modeling and has complex relationships with very large volume of data, or when you want complex calculations, scoping and named sets.

Adding a tabular model in Profitbase Studio 6

To add a new module of this type, right click the SSAS Databases (OLAP Databases) folder in studio and select Add SSAS Database. Select a tabular database template or select Add Empty.

On ‘Add Empty’, add name and select Type [Tabular SSAS Module] and click Finish (ref. adding content). Then add a module sub group (cube) and one or more or more module definitions(measure groups).

You can only add fact based measure groups (or tables), but when you bind this fact measure group to dimensions, each dimension will get their own measure group. 

From the table definition (measure group), you can select/edit columns and add calculated columns and measures. You can also define hierarchies and add KPI's (only for fact based measure groups).
Columns are fetched from the selected fact/dimension table. You can enable/disable and show/hide columns. You can also add calculated columns that refrences other columns in other tables or that has a formula. Measures is also added with a formula. So although your source has a measure, this is a regualr column and you have to make a new measure column from it, e.g Sum('amount source').

The Module Group/SSAS database settings has all the dimensions listed in the DW Dimension section. This is for the processing of tables/views in the data warehouse database. You can select member and reduce scan, to use a view directly on the source dimension or to use a wide version of a P/C dimension as for other module types. 

Limitations/recommendations:

• There are no graphical display of the tabular model in version 6.0. You can open the database in MS Visual Studio to view the model graphically.
• Use wide dimensions. P/C dimension can be used but there is no practical hierarchy viewing of parent/child structures.
• No support for drill through, many to many or referenced dimensions.
• Only on cube (by SSAS tabular design).
• Limited BL capabilities (no set, script etc). Calculated columns, measures and KPIs are supported.
• Module definitions based on dimension tables has limited support for operations; no Delete, Copy/Paste/Save as Template, browse etc.
• Translations not supported.

 

Tabular sales example

Goal for this sample is to give a walkthrough on how to a tabular model, show settings and operations to produce a browsable result.

Step 1 - add the module and cube

Right click the SSAS (OLAP) Databases folder in the Navigator and select Add SSAS Database.
From the add wizard, select Add Empty and click Next.
Enter Name, e.g. 'Tabular Sales', and select Type as 'Tabular SSAS Module..'. Optionally add a Description. Click Finish.

A new module has been added so select it in the Navigator. 

Here we have one Module Group [Tabular Sales] and we must check/edit connection settings.
As for all SSAS databases, we must set the connection to the Analysis Server and set the Database Name, e.g. 'pbSales'.
Also we must check the callback connection used by Analysis Server to connect to the data warehouse database. Check the user settings here.

A Tabular module can only have one Module Sub Group (Cube).  

Right click the 'Tabular Sales' module in the navigator and select New Cube.
From the add wizard, select Add Empty and click Next.
Select the cubes Name, e.g. 'Model'. Optionally add a Description. Click Finish.

A new module sub group or Cube has been added so select it in the Navigator. 

The Cube does not have any specific settings.

A Tabular Cube can have one or more tables (module definitions or measure groups). These can be based on dimensions or fact sources.
You can only add fact based tables. To add dimension tables, the fact table must use a dimension, and then the dimension table is added. 

Step 2 - add fact based table (as module definition/measure group) and its columns and BI

Right click the 'Model' module sub group (cube) in the navigator and select New Tabular Table. 
From the add wizard, select Add Empty and click Next.
Select the cubes Name, e.g. 'Sales Fact'. Optionally add a Description. Click Finish.

A new table (module definition or measure group) has been added so select it in the Navigator. 

First we need to add a fact source. Select the Fact Source Table section, click New, and select a Data Source Table from the dropdown in the Name-column.

Switch to the Column Definition section.

First time, columns are imported by following rules:

• On first import all columns are added as regular columns and some are hidden (see below). On changes later, columns are added but disabled.
• Added columns that are removed in source, will get get a red x as status.
• Dimension columns (e.g. those ending with ID) are set enabled, but hidden.
• For measure columns,  measures are added in two steps.
  First, the source column is added as a regular hidden column with name ending with 'Source' (e.g. 'Amount Source').
  Secondly, a measure column with Sum() formula against the source column is added. This is not hidden.

Columns are per default enabled. Decide which to enable/disable and witch to show/hide. Edit Display Names so that these are presentable and easy to use in formulas etc. Keep those dimension columns that we want to bind to dimensions enabled (those that ends with ID), but hide them

To set detailed settings for use in SSAS and its report clients. Select row and click Edit or double click row.

This dialog is also used to add additional columns. These can be of type calculated or measure columns.

In addition to BI you get through calculated and measure columns, you can add KPI and dw based columns (as for all modules).

A KPI can be added when we have one or more measure columns. Select Business Logic section and select New.. -> New KPI

KPI needs a base measure and a target value (fixed or measure). Configure indicator, style and its threshold values.

E.g. 

Step 3 - Add and edit  Dimensions

We now have the fact and BI definitions, and we must add dimensions. Select Dimension Usage section.

Click New..-> New Dimension Link, select a dimension and check column bindings. Repeat this for all dimensions.
Click New..->New Time Dimension Link to e.g. bind trans date to a time dimension.

 

When dimensions are added with bindings to the fact tables (or between different dimensions), they are also added as tables (measure groups).

Observe that the dimensions are added to the cube as tables and that the can be edited.

In the module group (SSAS database), the DW Dimension Settings section also gets the added dimensions.

This contains settings for how the dimension is handled in the data warehouse database. Select optional reduce and member scan settings, whether to use view or wide version of  P/C dimension.

 

For all dimensions we need to walk through editing of it to in order to add columns and hierarchies.
In SSAS multidimensional dimensions we use Attributes, but in tabular modules we use columns. So display name columns and hide id columns.

Select each dimension table under the cube.

E.g Currency

When you edit a dimension table the dimension is the source. The Source Info section contains info and links to editing and browsing.

The Column Definition section lists the columns.

This is a parent/child dimension.  So we hide ItemID column and disables the ParentItemID column. We renames the Description column to Currency.

Next we add hierarchies.

For most dimension, except Parent/Child dimensions/sub dimensions, we can import hierarchies.
To add manually, Click New and edit the name directly. Drag column to the hierarchy.

Step 4 - Process an check results

We have now completed the definitions and we must process the data warehouse.

Right click in the navigator and select Process or click Tasks and Data in the editing page.

 

Click on 'Rebuild Module in Data Warehouse' to create the tables/viewa in the data warehouse database.

Then click on 'Create & process SSAS database' to create and process the SSAS tabular database.

To check if the SSAS database is browsable, use the Status section and check the result.

To test the result use e.g. MS Sql Management Studio: