Guide for processing of Data Warehouse and SSAS databases

This tutorial contains some background and samples on what is necessary to get a processed consumable SSAS database.
To update/process, different tasks for SSAS and the data warehouse, must be run as described here.
In general, there are two types of changes:

• Configuration change – this is e.g. when a new column is added, a dimension attribute changed or new Business Logic (BL) added.
• Data change – this is e.g. when data is loaded from sources, and where these data must be processed through the data warehouse (DWH) and in the SSAS database.

But first some anatomy of SSAS.

Basic information of PS Module vs SSAS structure

Profitbase Studio Modules is organized according to the SSAS Multi Dimensional model. 

-  Module Group associates to the SSAS database

• Dimensions are shared in the Module Group and in the SSAS database.
• All tables/views with columns (the DW schema) are registered in the SSAS database.

-  Module Sub Group associates to SSAS Cube.

• All SSAS BI are defined in Calculation Scripts in a Cube. PS defines this in the Module Definition UI but it will be configured in the Cube.
• SSAS Actions are defined per Cube.
• Dimensions used in SSAS Measure Groups will also be defined in the Cube.

-  Module Definition associates to SSAS Measure Group.

• Fact tables and dimensions are associated to the Measure Group through Measures and Dimension Usage.
• Partitioning and Direct Queries can be associated with the Measure Group to optimize resources and aggregations.

For Tabular SSAS models, the same structure was used when introduced in Sql 2012, but now made much easier from Sql 2016. In general, the cube is replaced by a single ‘model’ and related measure groups called tables.  A table can either be fact or a dimension with focus on their columns. The BI is replaced by calculated columns and new KPIs.

Checks

Following tasks can be used to validate the current configuration and status:

• Run Module DVH Task: Validate Configuration. This checks for name conflicts, consistency and if tables/views exists.
• Check Module and SSAS status in Navigator (left image is dwh, right is SSAS).
• Check detailed process status of SSAS, by selecting status tab in any module pages. Click on refresh button to load status from SSAS.

This may give an indication of what’s needed, before you start changing configuration or processing data.

Configuration Changes

By configurational changes, we mean changes that affects settings in an SSAS database.

Settings can come from:

• Data Source tables; Chang of columns (added or removed columns) or a new table.
• Dimensions; Changes in Attributes, Relations, Hierarchies, All-level name, sorting, Unknown handling, time dimension range etc.
• Modules; Dimension Usage, Fact usage, Column Usage, Measures, BI, Actions, Advanced SSAS Settings, Connections, Span, Names, Storage Settings and Partitions.
• Scripts modifying settings.

In general, following Standard Tasks relates to configuration changes:

• Rebuild of Data Warehouse (dwh)
  • ‘Rebuild Module in Dwh’  - run a complete delete/rebuild of all tables and views in a module (dimension in module group and fact results in module definitions / measure groups).
  • ‘Auto Update Dwh’ – Recommended alternative to command above, but only processes if any changes in definition and data is detected. Dimension level can be included.
• ‘Delete and Rebuild SSAS DB’ – this recreates a complete new SSAS database

Detailed tasks for specific processing is described below.

Data Changes

By data changes, we mean changes that affects datasets in an SSAS database.

Data changes can come from:

-          Data Source tables; Load/Reload that results in changes, e.g new rows of data, or if data is edited manually.

-          Dimensions; Processing or modifications.

-          Modules; Module Dwh processing.

-          Scripts modifying data.

In general, following Standard Tasks relates to configuration changes:

-          Process SSAS DB – processes the complete database in full mode.

-          Auto Process SSAS – compares data changed dates in Dwh with last processed data in SSAS. Only necessary items is processed.

Detailed tasks for specific processing is described at below.

 

 

Detailed changes vs Advanced Module Dvh Tasks

Following cross reference can give an list of advanced tasks to run on specific changes.

		Changes in Data Source Fact Table				Changes in Dimensions		Changes in Modules
Ref		Data changed	Columns added	Columns removed	Table added / removed in module definition	Data changed (including mods, mdx)	Names, Attributes, Hierarchies All Level config	Module Group Dim. settings changed	Module Definition Fact BI, Column Usage	Module Definition storage settings
Module Group Tasks :
1	Validate configurations		(x)	(x)			(x)		(x)
2	Rebuild module in DW (recreate all tables/ views)		(x)	(x)	(x)	(x1)	(x)		(x)
3	Auto Process DW (recreate dimension and fact vies by changes in config. and data)		(x)	(x)	(x)	(x)	(x)	(x)	(x)	(x2)
4	Process fact views (recreate all fact views, module definitions)		(x)	(x)	(x)				(x)	(x2)
5	Process fact tables (recreate facts as tables using optional aggr. etc)		(x2)	(x2)	(X2)					(x2)
6	Create friendly named views
7	Run SQL script
Module Subgroup Tasks (Cube):
10	Process all child views (all module definitions)		(x)	(x)	(x)
Module Definition Tasks (Measure Group)
NA	No Module DW tasks/operations needed	X				X	X	X
20	Process fact views		X	X	X				X	X
21	Process dimensions					x	(x)
22	Process fact tables (aggregations)		(x2)	(x2)	(x2)
23	Update current partition (fact)
Shared Dimensions
30	Process dimensions (recreate all dims)						(x)
Specific Dimension
40	Process dimension					X	X		(x)
41	Full Reduce scan on Miodule Fact	(x)				(x)
42	Full Member scan on Module Fact	(x)				(x)
43	Get translations from source Dimension						X

 

• X = recommended
• x = alt. task (combination)
• 1 = depending on if dimensions (not default) or module definitions(default)  is Views.
• 2 = if tables/aggregations is used in Module Definitions (instead of default views).

Detailed changes vs Advanced SSAS Tasks (not completed)

Following cross reference can give a list of advanced tasks to run on specific changes.

		Changes in Facts (Mod.Def, Fact DS)		Changes in Dimensions		Changes in Cube (Mod.Sub Group)	Changes in Measure Group (Module Definition)
Ref		Data changed	Columns added / removed	Data changed (including mods, mdx)	Names, Attributes, Hierarchies All Level config	Actions BI	Module Definition Fact BI, Column Usage	Module Definition storage settings
Module Group/DB Tasks :
100	Delete and Rebuild SSAS DB (opt.process)		(x)	(x)	(x)	(x)	(x)	(x)
101	Process entire SSAS DB	(x)		(x)
102	Auto Process by Changes in DWH	X		X
103	Apply Translations				(x)		(x)
104	Create/Update data source tables and schema from DWH		X		X		X	(x2)
105	Create/update roles/permissions
106	Delete the SSAS DB
107	Backup SSAS DB
108	Restore SSAS DB
109	Run XMLA Scrip
Module Subgroup Tasks (Cube):
110	Create/update Cube						(x)
111	Process Cube	(x)
112	Apply Translations						(x)
113	Create/update BI in Cube					(x)	(x)
114	Create/update Actions in Cube					(x)
115	Delete the Cube
Module Definition Tasks (Measure Group):
120	Create/update Measure Group
	Process Measure Group	(x)
	Auto Process Measure Group	X
	Apply Translations						(x)
	Process Current partition	(x)						(x)
	Create/update specific partitions							(x)
	Process specific partitions	(x)
	Process partitions by time range	(x)
	Process by change in fact sources	(x)
	Process by change in specific fact source	(x)
	Delete the Measure Group
Shared Dimensions
	Create/update all dimensions
	Process all dimensions
Specific Dimension
	Create/update dimension
	Process dimension
	Apply Translations
	Delete the dimension

 

 TO BE COMPLETED..