SSAS and PowerBI Optimisation

Previous Scale Up and Scale Out	SQL Server Version and Edition Next

SQL Server Analysis Services (SSAS) ands PowerBI Server are important components in providing a scalable advanced 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.

Developing reports for an organisation is a complex process, and there is no magic bullet that will eliminate complexity and performance issues. Each site needs to decide where it will put the complexity, and deal with the management and performance issues that arise.

There are a number of ways in which SSAS and PowerBI can be used within an organisation to help manage these issues. The main methods of operation are shown below, along with their advantages and disadvantages. The items are grouped to show the optimal approach if the design complexity is focussed on the Schema or the Report:

Item	Optimal	Description
		Complexity Choices
Complexity Focussed on Reports	Report	Implications of focussing complexity into individual reports
Complexity Focussed on Schema	Schema	Implications of focussing complexity on the database schema
		SSAS Use
SSAS Tabular Mode with Live Connect	Schema	Combination of Live Connect and Columnstore Indexing provides fast query performance with minimal resource use
SSAS Tabular Model Design Using Perspectives	Both	Provides abstraction layer to tailor data presentation to the needs of a report
SSAS Tabular Mode with Imported Data	Report	Provides fast query performance but requires dedicated resource use
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	Schema	Key technology to providing fast query performance while minimising resource use
Data Mart Blue Green Deployment	Schema	Enables refresh of all data with under 2 seconds user disruption
		PowerBI Desktop Use
PowerBI with Live Connect to SSAS	Both	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

---

Complexity Focussed on Reports

This option allows report developers to use whatever techniques they wish to produce the visualisations required by the users. In general, reports can be developed with minimal DBA support, but a part-time Business Analyst will always be needed to resolve issues regarding presentation and unexpected data values.

This approach typically needs data to be imported into SSAS in order to provide acceptable performance. This in turn makes it difficult to include real-time data changes into reports.

A typical end result of focussing complexity on reports is:

• The number of DB schema objects is minimised
• A Star Schema approach needs to be used to assist in data filtering
• SSAS Perspective design is typically used to gain access to a table, with little use made of Hierarchies
• Internal report design tends to be complex, with little opportunity to reuse objects between reports
• Queries within reports will typically will join a number of tables, potentially giving performance issues
• SSAS and reporting CPU and memory use will be considerably higher compared to focussing complexity on schema

Top

---

Complexity Focussed on Schema

This option focusses on providing a curated set of objects within the SQL DB schema, with each family of reports using a specific set of database objects. The complexity of design within an individual report should be minimised, but ongoing report development will always require DBA support in order to maximise report performance.

The need for ongoing DBA support during report development is vital. Report developers will proceed at their own pace and find whatever way that works in order to produce their reports. Without ongoing DBA support to curate the schema the most likely result will be both a complex schema and complex and slow reports. As an estimate, each group of four report developers should have the services of one DBA. A part-time Business Analyst will always be needed to resolve issues regarding presentation and unexpected data values.

Where live connect is used to the SQL DB layer the DBA must be ready to enhance the schema to support DISTINCT and GROUP BY queries produced by Analysis Services. This is particularly important where columnstore indexes are used within SQL DB, as these perform badly with SQL2019 and below, and normally require an Indexed View to be established to provide adequate performance. If a future version of SQL Server allows DISTINCT queries to be pushed down into the columnstore index, the indexed views could be deleted without requiring any changes to reports.

A typical end result of focussing complexity on the schema is:

• Individual versions of Fact tables for each family of reports
• Wide tables designed to support Hierarchies within SSAS Perspectives to minimise joins when a report is run
• Extensive reuse of individual SSAS Perspectives, exploiting Hierarchies to provide data filtering
• Simple queries within reports that require few joins to provide the data filtering needed by the user
• SSAS and reporting CPU and memory use is minimised coampred to focussing complexity on reports

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.

Recommended Option: Yes

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
• Advantage: Real-time data changes can be included in reports relatively easily
• 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 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.

Recommended Option: Yes

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
• Disadvantage: Hierarchies may need to be supported by Indexed Views if columnstore indexes are used, in order to mitigate performance issues

Top

---

SSAS Tabular Mode with Imported Data

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

Recommended Option: No

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
• Disadvantage: Real-time data changes require a refresh of imported data before they can be included in reports

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.

Recommended Option: No

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.

Recommended Option: Yes

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
• Disadvantage: DISTINCT queries (as used by Hierarchies) do not perform well, and may require Indexed Views to mitigate performance issues

Top

---

Data Mart Blue Green Deployment

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

Recommended Option: Yes

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.

Recommended Option: Yes

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.

Recommended Option: No

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.

Recommended Option: No

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	SQL Server Version and Edition Next