Blog – Page 21 – Prologika

Power BI Large Datasets: The Good, the Bad, and the Ugly

November 10, 2019 / No Comments »

At Ignite 2019 Microsoft announced the public preview of large datasets in Power BI Premium. This is a significant milestone as now datasets can grow up to the capacity's maximum memory (previously, the max size was 10 GB with P3 plan), thus opening the possibility of deploying organizational semantic models to Power BI. I consider this feature mostly suitable for organizational BI as I don't imagine business users dealing with such large data volumes. I tested large datasets during its private preview, and I'd like to share some notes. The Good Today, BI developers can deploy organizational semantic models to three Analysis Services Tabular SKUs: SQL Server Analysis Services, Azure Analysis Services, and now Power BI Premium. SQL Server Analysis Services is the Microsoft on-prem offering and it aligns with the SQL Server release schedule. Traditionally, Azure Analysis Services has been the choice for cloud (PaaS) deployments. However, caught in the...
SQL Server 2019 Installation Woes

November 5, 2019 / No Comments »

Now that SQL Server 2019 is officially here, I was eager to try it out. My upgrade/reinstall experience ran into several issues that I thought might be worth sharing: Upgrade/reinstall fails with "An error occurred for a dependency of the feature causing the setup process for the feature to fail." With no indication what dependency failed. I solved it by extracting the iso file into a folder instead of mounting it. Analysis Services Tabular fails to start with "An error occurred when loading the 'ASSP', from the file, '\\?\D:\MSSQLSERVER\SSAS\Data\ASSP.0.asm.xml'." This error was caused by changing the Analysis Services Tabular default folders to another drive. It could be related to my setup, such as permissions granted to that drive. I fixed it by leaving the SQL Server and Analysis Services default folders. The Polybase services can't start and show perpetually "Starting" in the Windows Services applet. Consequently, the SQL Server Database...
Predict This!

November 2, 2019 / No Comments »

My wife bought a pack of replacement water filters from Amazon. It was tagged as "Amazon's choice". The product listing showed the manufacturer name and it had a nice product photo advertising genuine filters. Except that there were all fake, which we discovered quickly by the output water pressure. The water coming out of a filter should have lower pressure while there was no difference with the "genuine" filter as though there was no filtering going on at all. And the water had a bad aftertaste. So, we call the manufacturer. They compared the batch number from the package (manufactured in China) and found it fake. That filter could have had some Chinese poison in it and Amazon would have sold it under "Amazon's choice". BTW, when we reported this to Amazon, the product was listed under a different seller. There is a lot of noise (mostly vendor-induced propaganda as...
Atlanta MS BI and Power BI Group Meeting on November 4th

October 31, 2019 / No Comments »

MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on November 4, Monday, at 6:30 PM at the Microsoft office in Alpharetta. Andy Lawrence will share best practices for impactful Power BI Dashboards. CCG Analytics 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: Best Practices for Impactful Power BI Dashboards Date: November 4, 2019 Time 6:30 – 8:30 PM ET Place: Microsoft Office (Alpharetta) 8000 Avalon Boulevard Suite 900 Alpharetta, GA 30009 Overview: Power BI is gaining momentum as a preferred tool for dashboards and interactive reports. Let's revisit some best practices for dashboard development, such as: · The importance of form and function · Facilitating user adoption · Mistakes that everyone makes · Fast shortcuts for clean reports · Hidden settings that are lifesavers · Best Power BI updates...
Securing Power BI Report Pages

October 27, 2019 / No Comments »

Every now and then I get a question about how to secure Power BI report pages. A business analyst has created a multi-page report that requires limited access to some sensitive information in one or more report pages. I always cringe at this idea and recommend securing the data instead. That's because report-level or dashboard-level security can be easily compromised. For example, if the user has permissions to use Analyze in Excel, they will surely bypass report security and get access to all the data. The same will happen if they use Report Builder or connect a third-party tool to the published Power BI dataset. Instead, you should apply row-level security (RLS) to protect the data. Currently, Power BI doesn't let you secure report pages but if you must apply report or visual security, you have options: Break the report into two set of reports: one with open access and...
Filtering Power BI Visuals by Measure

October 21, 2019 / No Comments »

Power BI lets you filter the visual data by a measure, such as by a measure that dynamically calculates the customer's rank. This is convenient but there are performance implications related to the way Power BI autogenerates the DAX query. If a visual has a visual-level filter on a measure, Power BI Desktop defines a variable table filter that includes all measures in the visual. In the following example, the visual filters on the IncludedInRank measure, but the filter table includes all measures in the visual: VAR __ValueFilterDM1 = FILTER ( KEEPFILTERS ( SUMMARIZECOLUMNS ( 'Customer'[PARENT_NAME], 'Customer'[Label_DM], 'Customer'[VP_NAME], __DS0FilterTable, __DS0FilterTable2, __DS0FilterTable3, __DS0FilterTable4, "Vol_Gr_WKND", 'NRT_PRR_Parent'[Vol_Gr_WKND], "Vol_Gr_WE_Mon", 'NRT_PRR_Parent'[Vol_Gr_WE-Mon], "Vol_Gr_WE_Tue", 'NRT_PRR_Parent'[Vol_Gr_WE-Tue], "Vol_Gr_WE_Wed", 'NRT_PRR_Parent'[Vol_Gr_WE-Wed], "Vol_Gr_WE_Thu", 'NRT_PRR_Parent'[Vol_Gr_WE-Thu], "Vol_Gr_WE_Fri", 'NRT_PRR_Parent'[Vol_Gr_WE-Fri], "WTD_Gr_ptc", 'NRT_PRR_Parent'[WTD_Gr_ptc], "IncludedInRank", IGNORE ( 'NRT_PRR_Parent'[IncludedInRank] ) ) ), [IncludedInRank] = 1 ) As a result, these measures are evaluated twice: once in the filter table and twice in the main query (EVALUATE clause). This could...
Staying Cool

October 20, 2019 / No Comments »

A discussion overheard between two attendees at SQLSaturday yesterday: What is this Tabular thing? Like a cube but better. How's it better? It's in memory. I wonder if it can improve the performance of my report… It should, it's also in the Cloud. Note to myself. Use "in memory" and "cloud" more often.
Presenting at SQL Saturday Atlanta (BI Edition)

October 13, 2019 / No Comments »

SQL Saturday Atlanta (BI Edition) will be held on October 19th. I'll present a "Power BI Pro, or Premium: that is the question" strategy-oriented session at 12:30. One question that always pops us during my Power BI assessments for companies planning to adopt Power BI is which SKU to buy. The choice might seem obvious to your organization, but there is much more below the surface. Join this session and learn important considerations that might influence your decision, including: • Licensing cost • Hosting organizational semantic layers • External reporting • Features • On-premises reporting
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...