Blog – Page 25 – Prologika

Atlanta MS BI and Power BI Group Meeting on October 7th

October 1, 2019 / No Comments »

MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on October 7, Monday, at 6:30 PM at the Microsoft office in Alpharetta. Qubole will show us how Presto, Azure Data Lake, and Power BI can be used to analyze Big Data. Qubole will sponsor the meeting. For more details, visit our group page and don't forget to RSVP (fill in the RSVP survey if you're planning to attend). Presentation: Leveraging Power BI on Presto for the Azure Data Lake Date: October 7, 2019 Time 6:30 – 8:30 PM ET Place: Microsoft Office (Alpharetta) 8000 Avalon Boulevard Suite 900 Alpharetta, GA 30009 Overview: Presto is a distributed ANSI SQL engine designed for running interactive analytics queries. Presto outshines other data processing engines when used for business intelligence (BI) or data discovery because of its ability to join terabytes of unstructured and structured data in seconds, or cache queries intermittently for...
Atlanta MS BI and Power BI Group Meeting on September 10th

September 3, 2019 / No Comments »

MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on September 10, Tuesday, at 6:30 PM at the Microsoft office in Alpharetta. This is a meeting not to miss. A DAX founder, Jeffrey Wang (Principal Software Engineer Manager at the Power BI product group) is coming all the way from Seattle to share DAX best practices with our group! Captech Consulting will sponsor the event. For more details, visit our group page and don't forget to RSVP (fill in the RSVP survey if you're planning to attend). Presentation: Common DAX Patterns Date: September 10, 2019 Time 6:30 – 8:30 PM ET Place: Microsoft Office (Alpharetta) 8000 Avalon Boulevard Suite 900 Alpharetta, GA 30009 Overview: We will learn common DAX patterns by examining several issues frequently reported by DAX users through PowerBI customer support tickets. Users will be able to: 1. Learn from mistakes made by other DAX users...
Implementing Asymmetric Crosstab in Power BI

August 24, 2019 / No Comments »

A recent requirement called for an asymmetric crosstab report in Power BI. Transitioned to Adventure Works, the final report looks like this. The Internet column shows the sales amount from FactInternetSales. Then, the matrix pivots on the BusinessType column in the FactResellerSales. Because, Internet sales don't relate to BusinessType, it doesn't make sense to pivot it. Instead, we want to show Internet sales in a single static column before the crosstab portion starts. Implementing such a report in SSRS is easy thanks to its support of adjacent groups and static columns but not so much in Power BI. The issue is that Matrix would happily pivot both measures and the InternetSalesAmount would be repeated for each business type. Fortunately, with some blackbelt modeling and DAX, we can achieve the desired effect. I attached the pbix file and here are the high-level implementation steps: Add a ReportCategory table (you can use...
High Memory Usage and Calculated Columns

August 18, 2019 / No Comments »

Scenario: A client reports a memory spike during processing. They have a Tabular semantic model deployed to Azure Analysis Services. They fully process the model daily. The model normally takes less than 50 GB RAM but during processing, it spikes five times and Azure Analysis Services terminates the processing task complaining that it "reached the maximum allowable memory in our pricing tier". Normally, fully processing the model should take about twice the memory but five times? Solution: Upon expecting the model design, I discovered that the client has decided to add (many) calculated columns to the two fact tables in the model. Most of these columns are used to calculated variances to prior year. The formulas contain DATESYTD and other DAX date-related functions. After data is read, Tabular processes calculated columns, relationships and hierarchies. In this case, the spike was due to calculations involving large time ranges and ineffective DAX...
Gateway AD Lookups

August 11, 2019 / No Comments »

A data gateway is required for a cloud service, such as Power BI, to access on-prem data, either for live connections or for refreshing imported data. The gateway picked up an interesting feature to look up Active Directory on one property and return the value of another, which proved extremely useful for one client of mine. Scenario: An enterprise client has adopted a hybrid architecture I set up for them, where Power BI reports connect directly to an on-prem Analysis Services Tabular model. The issue is that besides the regular network login, every user has a cloud identity (the employee number) to access cloud data sources. Before the gateway enhancement, this required explicit per-user mappings in the gateway data source. Imagine you must do this for thousands of users. and you'll understand the maintenance issue they faced rolling Power BI across the enterprise. Solution: Instead of explicit mappings, all they...
Embedding Power BI Reports in JavaScript

July 27, 2019 / No Comments »

A recent engagement brought an interesting dilemma. An ISV wanted to embed Power BI reports in a SharePoint Online portal accessible by their customers. The app handles user authentication and authorization by following the "App owns data" pattern. However, apparently inspired by the Stalin's "No man, no problem" motto, SharePoint has decided to throw away custom server-side code, so we couldn't add an ASP.NET page to obtain the embed token. This left us with two choices: A pure JavaScript implementation – The main advantage of this option is that we could simply write a SharePoint client web part that handles the entire Power BI Embedded integration in JavaScript only. The major downside is that any JavaScript code is not secure, and this approach has a security vulnerability because you must store passwords in client-side code. Implement a separate app – You can implement a separate service, such as an Azure Function app...
MVP Again!

July 1, 2019 / No Comments »

Got awarded for the elite Microsoft Most Valuable Professional (MVP) - Data Platform award again. I've been recognized by Microsoft for my expertise and contributions to the community for every year since 2004! Learn more about the MVP program at https://mvp.microsoft.com/en-us/Overview.
The Power BI Viewer Role

June 27, 2019 / No Comments »

As I discussed in "Power BI Sharing is Getting Better" and "Power BI Sharing is Getting Better 2", the Power BI sharing saga has been improving and Microsoft is closing gaps. As recently announced, Microsoft added an important Viewer role that grants view permissions to the workspace content. The most important part in the announcement is: "Viewer role requires a Pro license or for the content to be in Power BI Premium. If your workspace is in Premium, users with Viewer role who don't have a Pro license can view the workspace in the workspaces list, navigate to the workspace, and view the content without getting a Pro Trial prompt." Therefore, the Viewer role lifts a significant limitation that forced you to use report/dashboard sharing or apps to share content out of a premium workspace with viewers. So, if you want to share the entire content of the workspace, now...
Why Is SQL Server Not Using My Index?

June 24, 2019 / No Comments »

Scenario: A partitioned Tabular table generates SELECT statements that queries a massive range from the base table on a date column. The processing job is taking long time. The SELECT query plan shows Clustered Index Scan but there is an index on the date column. Why is SQL Server not using the index? Surely, it will speed things up, right? This is where the blaming game starts... Analysis: SQL Server might ignore the index if the range is too wide. For example, these two queries will probably hit the index on the LastUpdated column in a 300 million rows table because the range is very narrow. select * from FactAccountSnapshot with (index(IX_FactAccountSnapshot_LastUpdated)) where LastUpdated = '1/1/2019' select * from FactAccountSnapshot with (index(IX_FactAccountSnapshot_LastUpdated)) where LastUpdated between '1/1/2019' and '1/2/2019' This query will probably not: select * from FactAccountSnapshot where LastUpdated between '1/1/2019' and '1/31/2019' Why? Because the server might determine that is much faster to scan the entire table instead of doing many lookups against...
Multi-level Column Unpivoting with Power Query

June 18, 2019 / No Comments »

An interesting challenge today come up while mentoring finance users in Power BI. Consider the following Excel layout. The user wants to unpivot the data by rotating the three header rows (Scenario Type, Month, and Year) from columns to rows. The issue is that the headers span three rows. If you just select these columns and unpivot, you'll end up with a mess. And Power Query operates on row at the time so you can't reference previous rows, such as to concatenate Scenario, Month, and Year. We can do the concatenation in Excel so we have one row with column headers, such as Actuals-Jan-2018, Actuals-Feb-2018, and so on, which we can easily unpivot in Power Query. But if we can't or don't want to modify the Excel file, such as to avoid the same steps every time a new file comes in? One way to achieve a Power Query-only solution is...