SSAS Archives - Ben's Blog

Export the results of best practice analyzer from all models

2024-04-09 Ben Comments 4 Comments

In this post, we will see how to automatically export the results of best practice analyzer for all our models. This post focuses on SQL Server Analysis, Azure Analysis Services, and Power BI datasets only if you have access to the BIM files. To automatically export the results of best practice analyzer from the Power BI service requires different approaches, especially on the authentication side which I won’t cover in this post. What is Best Practice Analyzer Best Practice Analyzer…

Read More Read More

Migrate SSAS to Power BI Premium

2024-03-08 Ben Comments 2 Comments

In this post, we will cover how to migrate SSAS to Power BI Premium using Tabular Editor. While there may be some more advanced techniques using scripting to Migrate SSAS to Power BI Premium this post focuses on a simple solution which is probably just fine for most small to medium-sized companies. Why migrate SSAS to PowerBI Premium instead of Fabric? Why Migrate SSAS to Power BI Premium instead of opting for Fabric? Well, surely migrating an on-premise architecture to…

Read More Read More

Analysis services 2022 distinguish models

Analysis services 2022 new features deep dive part 1: Composite Model and DAX

2022-10-04 Ben Comments 0 Comment

Since the release of Analysis services 2022 I never took the chance to explore its new features and improvements so in this post I will deep dive into each new and updated feature. The public preview of SQL Server Analysis Services 2022 was released on May 2022 and at the time of writing this post, SSAS 2022 is still in public preview. Analysis Services 2022: Why don’t we just migrate to Azure AS or PBI premium? One might ask if…

Read More Read More

Retrieve the MDX queries generated by Excel

2021-11-29 Ben Comments 4 Comments

When we use Excel to interact with an SSAS tabular model or a Power BI dataset via Analyze in Excel, Excel generates MDX queries which are then sent to the SSAS engine.So whenever a user is complaining about a pivot table connected to an SSAS or PBI dataset being too slow or showing wrong figures we may need to retrieve the MDX queries generated by Excel to investigate the issue. There are several ways to retrieve the MDX queries generated…

Read More Read More

Performance Benefits of Partitioning in Analysis Services Tabular

2021-06-28 Ben Comments 4 Comments

I previously wrote a post on how to dynamically partitioning tables in SSAS tabular using SQL, but in this short post, I will focus on the performance side of partitioning. What is Partition Partitions split a table into logical partition objects. Each partition contains a portion of the data, partitions can be processed in parallel independent of other partitions or excluded from processing operations if they don’t need to be refreshed. Why partitioning improves the refresh performance The main reason…

Read More Read More

Dynamically partitioning tables in SSAS Tabular model using SQL

2021-05-31 Ben Comments 3 Comments

In this post, I describe how we can dynamically partitioning tables in SSAS tabular using SQL.This approach works well for most of the simple scenarios where we need to keep control over the number of partitions to refresh daily or any frequencies. What is partition Partitions split a table into logical partition objects. Each partition contains a portion of the data, partitions can be processed in parallel independent of other partitions or excluded from processing operations if they don’t need…

Read More Read More

Data Dictionary Using DMV Queries

2021-01-16 Ben Comments 2 Comments

In this short post, I show how we can integrate a data dictionary using DMV queries within an SSAS tabular model and why we should do so. Why should we have a Data Dictionary within our model? The more complex a model becomes the more documentation we need to have about it. The more measures and complex formulas we have in a model the more calls/questions we have from end-users and thus the less time we can spend on getting…

Read More Read More