SSAS and PowerBI Optimisation

Previous Scale Up and Scale Out	Site SQL Files Folder Next

SQL Server Analysis Services (SSAS) ands PowerBI Server are important components in providing a scalable advances reporting environment. Each organisation needs to decide what resources they wish to dedicate to these components, and this section looks at how their use can be optimised to provide these capabilities while using the minimum possible CPU and memory resources.

There are a number of ways in which SSAS and PowerBI can be used within an organisation. The main methods of operation are shown below, along with their advantages and disadvantages. The recommended approach is shown in the Optimal column:

Item	Optimal	Description
		SSAS Use
SSAS Tabular Mode with Live Connect	Yes	Combination of Live Connect and Columnstore Indexing provides fast query performance with minimal resource use
SSAS Tabular Mode with Imported Data	No	Provides fast query performance but requires dedicated resource use
SSAS Tabular Model Design Using Perspectives	Yes	Provides abstraction layer to tailor data presentation to the needs of a report
SSAS MDX Mode	No	Legacy mode that provides fast query performance but requires dedicated resource use
		Data Mart Design
Data Mart Design Using Columnstore Indexing	Yes	Key technology to providing fast query performance while minimising resource use
Data Mart Blue Green Deployment	Yes	Enables refresh of all data with under 2 seconds user disruption
		PowerBI Desktop Use
PowerBI with Live Connect to SSAS	Yes	Provides fast query performance and startup with minimal resource use but has some design restrictions
PowerBI with Imported Data	No	Provides fast query performance but can be slow to start and maximises dedicated resource use
PowerBI with Direct Connect to SQL DB	No	Provides fast query performance at the expense of limited design capabilities

SQL FineBuild provides the following parameters to cap the amount of memory that can be used:

Parameter	Default	Description
/SetTotalMemLimit:	8000000000	Maximum memory allowed for SSAS instance, value given in bytes with default of 8GiB
/SetWorkingSetMaximum:	8000000	Maximum memory allowed for PowerBI Server (or SSRS if that installed instead), value given in KB with default of 8GiB

Top

---

SSAS Tabular Mode with Live Connect

This option minimises resource use, and when combined with Columnstore Indexing of the underlying data provides query performance comparable with dedicated SSAS database performance.

Characteristics of this option:

• Causes SSAS to pass all data queries through to the underlying data store
• SSAS database consists of metadata only so disk and memory use is minimal
• Model deployment typically takes under a second, no further procesing needed
• Underlying data store needs to be in columnstore format to provide good user query performance
• Advantage: SSAS uses minimal disk, memory and CPU resources
• Disadvantage: No support for computed columns and joins can only reference a single column, these items must be pushed down into the data layer

Top

---

SSAS Tabular Mode with Imported Data

This option provides fast query performance but requires dedicated resources, and data refresh times can be problmatic.

Characteristics of this option:

• Causes SSAS to import all data from the underlying data store into a SSAS database
• SSAS database requires disk and memory resources for all data even when it is not being used
• Model deployment needs scheduling as it results in an empty database that needs time allowed for processing to load data
• Underlying data store can be in any format as all data imported into SSAS to provide good user query performance
• Advantage: Computed columns can be used, also provides better support for legacy query platforms
• Disadvantage: Resource use and refresh time can prove problematic for many organisations

Top

---

SSAS Tabular Model Design using Perspectives

This option provides an abstraction layer to tailor data presentation to the needs of a report or a group of reports.

Characteristics of this option:

• Allows the creation of report-specific data relationships
• Allows hard-coding of critical key values for data retrieval
• Allows end-user report building on top of a specific perspectives
• Allows row-level security to be baked in to a perspective via views at the data mart level
• Advantage: Provides layer of abstraction between Data Mart and PowerBI Visualisation allowing re-use of defined objects
• Disadvantage: Requires staff familiar with Perspectives to take best advantage of this feature

Top

---

SSAS MDX Mode

This option is now considered a legacy format. It provides fast query performance but requires dedicated resources, and data refresh times can be problmatic.

Characteristics of this option:

• Now considered a legacy option, no updates since 2017 and is not available in Azure
• Normally all data from the underlying data source imported into a SSAS database, but ROLAP queries can directly access underlying data store
• SSAS database requires disk and memory resources for all data even when it is not being used
• Model deployment needs scheduling as it results in an empty database that needs time allowed for processing to load data
• Underlying data store can be in any format as all data imported into SSAS to provide good user query performance
• Advantage: Computed columns can be used, also provides better support for legacy query platforms
• Disadvantage: Resource use and refresh time can prove problematic for many organisations

Top

---

Data Mart Design Using Columnstore Indexing

This option is a key technology to providing fast query performance while minimising resource use.

Characteristics of this option:

• Query performance comparable to SSAS database performance
• Columnstore index only holds unique values for each column, rows reconstructed using vector tables
• Columnstore format encourages a 'wide row' design that eliminates many joins and further improves query performance
• Advantage: No need to load data into SSAS, therefore avoiding duplication of disk and memory resources
• Advantage: Supports distributed read-only queries when database part of an Availability Group
• Disadvantage: Requires staff to use a different mindset to row-format design to get best performance

Top

---

Data Mart Blue Green Deployment

This option enables the refresh of unlimited amounts of data with under 2 seconds user disruption.

Characteristics of this option:

• Best suited to periodic data refreshes, such as End of Day processing
• All data updates performed to Blue copy of the database, therefore no interruptions to user queries regardless of data load time
• All data queries performed against the Green copy of the database
• Advantage: Swapping Blue and Green databases typically takes less than 2 seconds, resulting in minimal user disruption
• Disadvantage: Requires disk resource to hold both the Blue and Green copies of the database

Usage Details

A suggested workflow that takes advantage of Blue/Green deployment is given below:

1. SSAS is operating normally, with all queries targeted to the [DataMart.Green] database

2. Take Snapshot of source OLTP databases, to provide stable base for subsequent ETL

   If a database does not contain a memory-optimised file group, then all file groups and files can be included in the snapshot

   If a database does contain a memory-optimised file group it can be the subject of a snapshopt, but the memory-optimised file group cannot be included in the snapshot

3. Perform ETL processes, writing output to [DataMart.Blue] database

4. Drop Snapshot of OLTP databases

5. At time of Deployment, run the following script:

   ALTER DATABASE [DataMart.Blue]  SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
   ALTER DATABASE [DataMart.Green] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; -- SSAS unable to access DataMart
   ALTER DATABASE [DataMart.Green] MODIFY NAME = [DataMart.Red];
   ALTER DATABASE [DataMart.Blue]  MODIFY NAME = [DataMart.Green]; 
   ALTER DATABASE [DataMart.Green] SET MULTI_USER;                          -- SSAS now operating normally again
   ALTER DATABASE [DataMart.Red]   MODIFY NAME = [DataMart.Blue];
   ALTER DATABASE [DataMart.Blue]  SET MULTI_USER;
   

Top

---

PowerBI with Live Connect to SSAS

This option provides fast query performance and startup with minimal resource use but has some design restrictions.

Characteristics of this option:

• Causes PowerBI to pass all data queries through to the underlying data store
• Allows underlying SSAS Model to be refreshed with no need to process a data refresh
• When coupled with columnstore indexing in the data mart, gives performance comparable to imported data
• Advantage: Requires minimal memory and data storage to support a Visualisation
• Disadvantage: Calculated columns have to be pushed down into the data mart layer
• Disadvantage: Multi-column joins have to be resolved as compound columns in the data mart layer

Top

---

PowerBI with Imported Data

This option provides fast query performance but can be slow to start and maximises dedicated resource use.

Characteristics of this option:

• Causes PowerBI to import all data into the internal SSAS instance
• Start-up time can be slow while data import processes
• Performance after start-up is comparable to Live Connect on top of columnstore data mart
• Advantage: Allows calculated columns and multi-column joins at the visualisation layer
• Disadvantage: Requires more memory and disk storage than any other use of PowerBI

Top

---

PowerBI with Direct Connect to SQL DB

This option provides fast query performance at the expense of limited design capabilities.

Characteristics of this option:

• Causes PowerBI to pass all data queries through to the underlying data store
• When coupled with columnstore indexing in the data mart, gives performance comparable to imported data
• Advantage: Requires minimal memory and data storage to support a Visualisation
• Disadvantage: All relationships, etc contained within the perspective, no opportunity for re-use
• Disadvantage: Only a sub-set of PowerBI features can be used

Copyright FineBuild Team © 2021. License and Acknowledgements

Previous Scale Up and Scale Out	Top	Site SQL Files Folder Next