SSAS and PowerBI Optimisation

Jump to bottom

EdVassie edited this page Mar 4, 2021 · 6 revisions

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

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

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

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
Disadvantage: Requires staff familiar with Perspectives to take best advantage of this feature

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

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

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

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

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

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
TBC

Copyright FineBuild Team © 2021. License and Acknowledgements

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