Blog – Page 30 – Prologika

Demystifying Tabular Object Level Security (OLS)

April 15, 2018 / No Comments »

Object-level security (OLS) is a frequently requested security feature when implementing semantic models. For example, a current project disallows some sales persons to see sensitive measures, such as Margin and Profit. In Multidimensional, modelers could use cell security to meet such requirements with the risk of compromising performance. Starting with SQL Server 2017 (compatibility level 1200), Tabular supports object-level security to disallow access to entire tables or specific columns without a performance penalty. As it stands, OLS has the following limitations (for a full list, review the documentation): There is no user interface for defining OLS. Instead, after defining your roles and row filters as you'd typically do, you need the extra step to open the model.bim source code and enter the OLS definition manually. For example, this Users role definition disallows access to the GrossProfit column. Fortunately, SSDT preserves OLS when you make changes to the role in the Role...
Another Successful Power BI Workshop

April 13, 2018 / No Comments »

Thank you to everyone attending the Power BI workshop by Prologika yesterday. We had great turnout with more than 50 people from 30 companies attending the event at the Microsoft Technology Center in Alpharetta! The key takeaways were: Many companies transition to Power BI because of its great value-to-cost proposition. PowerQuery is a fantastic tool for data cleansing and transformation that is missing in other competitor offerings. Power BI can deliver rich visualization capabilities that delight end users and surpass by far the capabilities of traditional reporting tools. Instead of being a just a stand-alone BI tool, Power BI is a part of a rich ecosystem of products and services that allows you to extend Power BI in versatile ways.
Notes on Analysis Services Tabular Performance and Parallelism (Part 2)

April 9, 2018 / No Comments »

In my "Notes on Analysis Services Performance and Parallelism" blog, I shared limited performance results about scaling up Azure Analysis Services Tabular. As I mentioned, I expect the same findings to apply to SQL Server Analysis Services Tabular for on-premises deployments. As the official load test results came in, I'm ready to provide more info and general recommendations. We load tested the AAS semantic model mentioned in the previous blog (about 2 billion rows) by capturing a representative subset of DAX queries including slow, medium and fast queries. We used a stepped load starting with one user and increased the number of concurrent users with one every 30 seconds. We considered the server saturated when 90% of maximum QPU was reached on average. For example, the S4 performance level has 400 QPU (20 cores) so the saturation level would be 360 QPU on average. Note It takes a while for the Azure...
Notes on Analysis Services Performance and Parallelism

April 4, 2018 / 1 Comment »

As a part of a semantic model assessment, I got to experiment with different configurations of Azure Analysis Services. In this case, the largest fact table has almost 2 billion rows so it's a good size model. I was particularly interested the correlation between a higher performance tier and query performance. One thing that I like about PaaS is that it's easy to change and experiment with different configurations. The thing that I don't like is that I can't peek under the hood. This is probably done on purpose to keep people like me out, just like these green nets surrounding construction zones. So, I don't know what hardware AAS is running on and how it's configured. And I failed to get clarification from Microsoft. So, I might be out of line here, but I thought my findings are worth sharing albeit not officially vetted. AAS Basic and Standard tiers...
Power BI Models Scaling Up

March 21, 2018 / 6 Comments »

Chris Webb wrote a great post today "Power BI Roadmap Announcements In the Dynamics 365 Spring '18 Release Notes" citing the many exciting new features coming to Power BI in the next few months and highlighting the huge momentum behind Power BI. I want to bring your attention to a couple of them: Incremental data refresh: Organizations can use incremental data refresh to scale datasets to even larger sizes. With incremental data refresh, users can load only new or changed data. When creating a dataset in the Power BI Desktop, a user configures a refresh table for each table in the model that is to load incrementally, and then publishes it to a Power BI Premium workspace. Thereafter, each scheduled refresh loads only new or changed data. Query acceleration for large datasets (Public Preview): Users can create DirectQuery models over any size data in sources, such as Spark and Azure...
Atlanta MS BI and Power BI Group Meeting on March 26th

March 21, 2018 / No Comments »

MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on Monday, March 26th at 6:30 PM. Leo Furlong will introduce you to implementing organizational semantic models with Azure Analysis Services. BlueGranite will sponsor the meeting. And your humble correspondent will demo several latest Power BI features: persistent filters, tooltip pages, and share to anyone. For more details, visit our group page and don't forget to RSVP (use the RSVP survey on the group page) if you're planning to attend. Presentation: Introduction to Azure Analysis Services Date: March 26, 2018 Time 6:30 – 8:30 PM ET Place: South Terraces Building (Auditorium Room) 115 Perimeter Center Place Atlanta, GA 30346 Overview: Azure Analysis Services (AAS) is an exciting tool within the Azure Data Services tool set. In this session, we’ll learn about the basics of AAS including how to create the service in the Azure portal, options for developing solutions and...
Too Many Measures?

March 18, 2018 / No Comments »

I'm doing an assessment of a data mart and semantic layer for an organizational BI solution. I was given the source of the Analysis Services Tabular model. Upon opening it locally, it took Visual Studio several minutes to open the project. Even worse, after the project was finally loaded, my laptop CPU utilization went into 80-90% and all the 16 GB of memory got exhausted rather quickly. With an empty workspace database! What's going on? As it turned out, a business requirement asked for all measures to be in the same table. So, developers introduced a Metrics DAX calculated table and assign it as a home table for all measures organized in display folders. Nothing wrong with that. However, the number of DAX measures were 2,774! It's common practice to "flatten" DAX measures, such as to have separate measure for time calculations: SalesAmount, SalesAmountQTD, SalesAmountYTD, and so on. This can...
Using Profiler to Trace Azure Analysis Services

March 11, 2018 / 1 Comment »

Scenario: You use Azure Analysis Services and you want to trace events in the SQL Server Profiler. However, you run the Profiler, connect to AAS, and you get greeted with "Unable to locate trace definition file Microsoft Analysis Services TraceDefinition 15.0.0.xml". The prompt gives you an option to download the trace definition from the server, but nothing happens. You'll encounter the same issue if you attempt to access a higher version of AAS or Database Engine from a lower version of Profiler, such as when you install SSMS 2012 on your laptop, but you connect to SQL Server 2016 or higher. Workaround: While we're waiting for Microsoft to provide the Azure AS trace definition, you can use this workaround: Navigate to the Profiler trace definition folder. In my case this folder is C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Profiler\TraceDefinitions. Copy the latest trace definition file (in my case it was "Microsoft Analysis...
Azure SQL Database Managed Instance – the Good, the Bad, the Ugly

March 6, 2018 / No Comments »

Azure SQL Database Managed Instance is available for public preview (for pricing see this page). It will play a very important role in the SQL Server cloud PaaS derivatives, which are now four: Azure SQL Database – A database-scoped service available in Basic, Standard, and Premium performance tiers. Azure SQL Data Warehouse – A scalable farm of Azure SQL databases that has roots in Microsoft Analytics Platform System (APS). Elastic Pools -- A pool of Azure SQL databases that share the same resources and it's particularly useful for multi-tenant scenarios. (NEW) SQL Database Managed Instance – Similar to an on-premises SQL Server, SQL Database Managed Instance allows you to host multiple database under the same instance. A customer used SQL Database Managed Instance while it was in private preview and I followed its evolution closely. Here are few notes: The Good The good is in the name. It's common to...
DAX Rank Over Two Columns

March 6, 2018 / No Comments »

Scenario: An interesting scenario came today from a client. Consider the following schema. You have a fact table InternetSales and two dimension tables (Customer and SalesTerritory). You want to produce a report ranking each customer based on their sales for each territory. In addition, you want the rank to change as you filter the report, such as filter specific customers or specific territories, so you need a measure. Solution: The DAX RANKX function takes a table as the first argument. However, in this case, the rank needs to be produced over fields from two tables. So, the CROSSJOIN function is used to produce the needed combination. 'InternetSales'[CustomerRank] := IF ( ISBLANK([SalesAmount]) || NOT HASONEVALUE(Customer[FullName]), BLANK(), RANKX(CROSSJOIN(ALLSELECTED('Customer'[FullName]), ALLSELECTED('SalesTerritory'[SalesTerritoryCountry])), [SalesAmount],,,DENSE) ) The ALLSELECTED function is needed to ignore the context from the FullName and SalesTerritoryCountry from the respective columns on the report. The condition in the IF statement ignores calculating the rank for customers...