Blog – Page 35 – Prologika

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...
Prologika AppSource Consulting Offers

February 28, 2018 / No Comments »

Microsoft selected and published two Prologika Power BI-related consulting offers on Microsoft AppSource. And their price is right; they are both free! Power BI for Your Organization: 2 HR Assessment – Prologika will evaluate your data analytics objectives to determine how Power BI can empower your organization to understand better and improve its business. Power BI for Business Performance: 2-Hr Briefing – Prologika will provide you with a free, 2-hour briefing on how our client derived tremendous value from our solution for Business Performance Management. Learn how Prologika transformed the way a large organization does financial planning by creating and comparing budgets and forecasts with our customized solution based on the Microsoft Data Platform and Power BI.
2018 Gartner Magic Quadrant for Analytics and BI Platforms

February 27, 2018 / No Comments »

The 2018 Gartner Magic Quadrant for Analytics and Business Intelligence Platforms is out. I read the report and followed the dots. I personally hoped to see the Microsoft dot higher than the overrated Tableau, given everything I said here, here, and here. I'm glad that Gartner finally realized what I have been saying all along that Tableau lacks "complex data model support" among many other things. Anyway, this is a great endorsement of how far Power BI has come. And about what Microsoft can do when product groups work together. Mind you, the main focus of the Gartner's evaluation was Power BI. The organizational BI pieces (SQL Server, SSRS, SSAS, SSIS, Azure) were not considered. Here are my thoughts about the Microsoft-related cautions cited by Gartner: Mode 2 analytics, Azure cloud only – "Power BI has mainly focused on the requirements for Mode 2 (agile, self-service) analytics, while its on-premises SQL Server Reporting Services serves the needs...
Quadrants Here, Quadrants There…

February 26, 2018 / No Comments »

While we're waiting for the Gartner 2018 Magic Quadrant for Data Analytics (should be out any moment), two others related to data analytics were released. 2018 Magic Quadrant for Data Management Solutions for Analytics This one evaluates "complete software system that supports and manages data in one or more file management systems (usually databases)". So, think of traditional and cloud-based data warehousing. I'm surprised to see AWS ahead of Microsoft and Oracle in the ability to execute, given that Amazon doesn't have on-premises offerings (see the ML quadrant below where apparently that's important). 2018 Magic Quadrant for Data Science and Machine-Learning Platforms This one evaluates "vendors of data science and machine-learning platforms. These are software products that data scientists use to help them develop and deploy their own data science and machine-learning solutions." I personally don't agree with Gartner on the Microsoft Visionary rank, given all the investments Microsoft has...
Unblocking MDX Queries from SSRS Reports

February 23, 2018 / No Comments »

I love performance optimization. It's exciting to peek under the hood and take a deep dive in the mysterious world of how Microsoft designed things. And it always humbles me by bringing forth gaps in my knowledge. Scenario: You have SSRS reports that query Analysis Services Multidimensional cubes. When users request reports in Report Manager (aka Web Portal starting with SSRS 2016) and … the report hangs sporadically, without even getting to the ReportViewer page… for minutes. The server doesn't appear to be under resource pressure. The issue happens when the cube is being processed. And no, this is not about locking. Solution: This one wasn't trivial to figure out. The profiler showed that when the report is requested, SSRS fires the first MDX query (typically, a query that populates the parameter available values). The breakthrough came when I executed the same query in SSMS while the SSRS one was...
Atlanta MS BI and Power BI Group Meeting on February 26th

February 22, 2018 / No Comments »

MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on February 26th at 6:30 PM. Stacey Jones will explain how Power BI can integrate with other systems, such as Dynamics and Salesforce. SentryOne will sponsor the meeting. And your humble correspondent will show you two latest Power BI features: report sharing and date tables. 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: Integrating Power BI with Dynamics and Other Solutions Level: Intermediate Date: February 26, 2018 Time 6:30 – 8:30 PM ET Place: South Terraces Building (Auditorium Room) 115 Perimeter Center Place Atlanta, GA 30346 Overview: Microsoft has differentiated itself for many years as the company that not only sells software, but software that works well together. For example, many companies sold suites of office productivity software, but none sold...
Tableau Hyper vs. Power BI xVelocity

February 18, 2018 / 6 Comments »

To compete more effectively with Power BI, Tableau acquired a database technology called HyPer. Like the Microsoft xVelocity in-memory technology (formerly known as VertiPaq), which powers Analysis Services Tabular, Power BI, and SQL Server Columnstore, HyPer is an in-memory database system. Hyper was initially developed as a research project at the Technical University of Munich. Tableau explains that "with Hyper, transactions and analytical queries are processed on the same column store, with no post-processing needed after data ingestion. This reduces stale data and minimizes the connection gap between specialized systems. Hyper's unique approach allows a true combination of read-and write-heavy workloads in a single system. This means you can have fast extract creation without sacrificing fast query performance. (We call that a win-win.)". Tableau shipped Hyper with Tableau v10.5. Here, we have a statement that Tableau claimed that Hyper achieved 3x faster data extraction and 5x increase in query performance...