Press "Enter" to skip to content

Category: Analysis Services

Releasing a Tabular Model without Users or Roles

Olivier van Streenlandt hit a deployment problem:

A couple of weeks ago my team & I ran into an issue with SQL Server Analysis Services (SSAS), due to a network split between companies, We weren’t able anymore to manage our SSAS access into our SSAS Tabular Model. Since deploying a Tabular Model using Visual Studio is also overwriting members & roles, we needed to find a valid alternative to execute our deployments. Manually at first and automated in the end.

Read on to see how they used Azure DevOps pipelines to solve the issue.

Comments closed

Power Automate and Dataset-Driven Power BI Subscriptions

Dan English follows up on a prior topic:

In the last post I went over using Power Automate to perform a data driven report subscription using a Paginated report referencing an AAS database. The flow referenced an Excel file with the information to make the process data driven and generate 2000 PDF files that could then be emailed to users. In the flow the PDF files were simply placed in a OneDrive folder for testing purposes to validate the flow would run as expected and to review the metrics after the fact to evaluate the impact of running the process.

For the follow up there were two items that I wanted to compare against the original flow

1. Moving the AAS database being referenced to a Power BI dataset hosted in the same capacity as the Paginated report

2. Using a Power BI report instead of a Paginated report

In this post I will cover the first comparison. 

Check out what changes and what stays the same between using Azure Analysis Services and Power BI-hosted datasets.

Comments closed

Object-Level Security in Power BI and Analysis Services

Marco Russo and Alberto Ferrari take advantage of object-level security:

To be more technical, object-level security can be applied to tables and columns, but it cannot be applied to measures directly. If a measure – whether in a direct or indirect way – references a column or a table that is not accessible under the current security context, the measure becomes invisible as well. This way, it is guaranteed that if a column must be invisible to a group of users, its content cannot be inferred by looking at the result of measures based on said column.

Concretely, there may be cases where a measure should be hidden from a group of users without removing the visibility of existing data structures. Let us look at a simple example first. We define the Sales Amount measure as the product of Sales[Quantity] by Sales[Price]. You also have a Discounted Sales measure that applies a set discount Sales Amount; now how can you hide Discounted Sales from a group of users without hiding the initial Sales Amount measure? By hiding either Sales[Quantity] or Sales[Price], you would hide both measures. Because the discount is set inside the Discounted Sales measure and not stored in the model, it looks as though you cannot hide just the measure. However, it we create a dependency in Discounted Sales on an empty hidden table specifically created to generate that dependency, we can hide Discounted Sales by hiding that table.

Read on to see how.

Comments closed

Making the Case for Azure Analysis Services

Teo Lachev makes the case:

Microsoft BI practitioners have three options for hosting semantic models: SSAS (on prem), Azure Analysis Services (cloud), and Power BI (cloud). AAS is somewhat caught between a rock and a hard place. Given that Power BI gets the most attention for cloud deployment, why would you consider AAS at all? There are two main reasons:

Read on for the reasons. Knowing how much it does cost, it almost feels like trying to thread a needle: if you don’t spent enough or have enough data, Power BI is typically much more efficient; if you have sufficient data, I’d want to do a proper cost analysis between on-prem (or IaaS) Analysis Services and Azure Analysis Services.

Comments closed

Handling Merge Conflicts with SSAS Tabular Projects

Richard Swinbank fights Visual Studio:

I sometimes find working with Visual Studio’s projects a challenge in multi-developer environments, because each project type seems to have its own vulnerability to Git merge conflicts. In the case of SSAS tabular, I’ve found two issues to be a regular source of conflicts:

Click through to see what those two causes are and what you can do to reduce the risk of having either one burn you.

Comments closed

Switching Connections from AAS to Power BI

Marc Lelijveld wants to swap a connection from using Azure Analysis Services to Power BI Premium:

Having the context of an Azure Analysis Services dataset that is migrated to Power BI Premium, you might have to rebind many reports. Especially if this dataset is positioned as being a managed dataset that is also used for self-service purposes and has many related reports.

In this blog I will elaborate on how you can easily rebind all these reports to the new Power BI dataset, without downloading all reports and manual rebinding.

It’s not a trivial operation, but it is a lot easier than updating each entry individually.

Comments closed

Comparing Azure Analysis Services and Power BI PPU on Cost

Gilbert Quevauvilliers hits on an important factor:

One of the most important aspects when looking to migrate from AAS to PPU is what will costs be. This plays an important part in the decision.

In this blog post instead of doing an AAS and PPU comparison I am going to do this side by side, which I feel will make it easier to compare.

In my opinion it is only fair to compare the same sizing between AAS and PPU, and this can only be done by using the Memory allocation.

Click through for the analysis.

Comments closed

Row Level Security in Azure Analysis Services and Power BI PPU

Gilbert Quevauvilliers continues a series on moving from Azure Analysis Services to Power BI Premium Per User:

In this blog post I am going to cover how to implement Row Level Security (RSL) when using AAS and how this can be done on PPU.

In my example below I am going to show creating to simple RLS roles which will limit data for the users who belong to 2 roles.

Despite the simplification, we can see how row-level security applies to both products and how the two differ.

Comments closed

Representing Dates in Power BI: Date or Integer?

Marco Russo and Alberto Ferrari share their take on a classic debate:

A question that is often asked during the design of a Power BI data model is whether it is better to use an Integer or a Datetime column to link a fact table with the Date dimension. Historically, using Integers has always been a better choice in database design. However, Tabular is an in-memory columnar database, and its architecture is quite different from the relational databases we might be used to working with.

Indeed, in Tabular there are no technical differences between using a Datetime or an Integer to create a relationship. The database size, the query speed, and any other technical detail are absolutely identical. Therefore, the choice is not related to technical aspects, but rather on the convenience of the design. Depending on the specific requirements of your model, you might favor one data type against the other. In the most common scenarios, a Datetime proves to be better because it provides more possibilities to compute values on dates without having to rely on relationships. With that said, if your model uses Integers and you do not need to perform calculations on the dates represented in the table, then you can choose the most convenient data type – that is, the one already used in the original data source.

The remaining part of the article aims to prove the previous sentences, and to provide you with the technical details about how we tested the respective performance of the two options.

Click through for Marco and Alberto’s analysis, noting that “date” here does not include time of day, so it would have the same cardinality as the integer date key. This was a more important thing fifteen years ago, before columnstore technologies (like columnstore indexes and VertiPaq) were readily available and that 4-byte integer was considerably smaller than an 8-byte DATETIME.

Comments closed