Blog – Page 12 – Prologika

Atlanta MS BI and Power BI Group Meeting on December 6th

November 29, 2021 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, December 6th, at 6:30 PM ET. To finish the year at a high note, the famous Guys in the Cube (Patrick and Adam) will tell us how keep the data fresh in Power BI. And your humble correspondent will update you on the Power BI latest. For more details and sign up, visit our group page. Presentation: Keeping your Data Fresh in Power BI Date: December 6th Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: We all want our data refresh to happen quickly so the most current data is available for our reports. In this session we will walk you through options to configure refreshing your data but more importantly we will help with performance. We’ll look at how to identify bottlenecks and then how to...
Power BI Bookmark Navigator – A Better Hack

November 17, 2021 / No Comments »

As a report author, you are constantly pressed to fit more visuals into a single page. The November release of Power BI Desktop introduced the Power BI Bookmark Navigator, which simplifies the process of creating a tabbed interface, such as this one. Since Power BI doesn't support visual containers or a "menu" visual, you must resort to the awful hack of hiding and showing UX elements by bookmarking them. This reminds me of the beginning of my career as a developer where we didn't have widgets and we had to hack our way through implementing a custom navigation "experience" by toggling visibility. Alas, this continues in the 21st century but at least the hack got simplified. To implement the tabbed interface: Add two (or more) overlapping visuals. Add two bookmarks (Bar Chart and Column Chart) that show and hide the appropriate visual. Don't worry about hidden visuals impacting the report...
“Serverless” Lessons Learned

November 5, 2021 / No Comments »

I've architected and currently implementing a solution that uses Synapse (my last newsletter has the details, plus the architecture diagram). Synapse Serverless is the Microsoft answer to Amazon Athena but instead of using open-source tools like Presto, it's built on SQL Server. In this project we extract many tables from 1,500 on-prem SQL Server databases and stage them in ADLS. From there we use Synapse Serverless to virtualize these files as tables that we query with T-SQL to load the source "table" data into a data warehouse hosted in Synapse SQL Pool. I have to tell you that I'm becoming a "serverless" fan. Here are a few lessons learned from this project: Save the files in parquet format in ADLS. Parquet can be compressed. It's columnar based, it's much faster to query. Serverless automatically creates statistics for parquet files on the first query and each time it detects changes. Less...
Atlanta MS BI and Power BI Group Meeting on November 1st

October 30, 2021 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting today (Monday, October 4th), at 6:30 PM ET. Sandeep Pawar will explain how to use the Power BI AI visuals for predictive insights. And your humble correspondent will show you how to use the Power BI REST APIs. For more details and sign up, visit our group page. Presentation: Demystifying Power BI AI Visuals Date: November 1st Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: Power BI has several powerful AI visuals that allow business analysts to create insightful reports that include predictive capabilities without writing any code. In this session, we will take a deeper look at these visuals, discuss how exactly they work, when & how you should use them effectively and importantly when not to use them. We will look at the algorithms driving them and...
PolyBase Adventures

October 12, 2021 / No Comments »

I'm setting SQL Server 2019 PolyBase for ODBC to JDBC access to a vendor data lake to virtualize entities as SQL tables. Overall, a smooth experience with a few gotchas: Data type mappings The vendor lake uses Oracle data types TIMESTAMP AT TIME ZONE and BOOLEAN that Java doesn't know how to map. The solution was to set up a view in the data lake (luckily the vendor supports that) to cast these data types to NVARCHAR and INTEGER. NullPointerException Once the table is finally set up what do we get when querying it? 105082;Generic ODBC error: java.lang.NullPointerException . How do we fix this horrible issue? Upgrade SQL Server and PolyBase to the latest cummulative update (CU). The final mystery that I haven't been able to crack yet is that for some obscure reason, PolyBase adds quite a bit of performance overhead to the query execution. So, if a query...
Atlanta MS BI and Power BI Group Meeting on October 4th

October 4, 2021 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting today (Monday, October 4th), at 6:30 PM ET. Reda Raz (RADACAD) will share best practices on semantic modelling with Power BI. And your humble correspondent will show two new features: Get Insights and Power BI Goals. For more details and sign up, visit our group page. Presentation: Power BI Modeling 101 Date: October 4th Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: Getting started with a report in Power BI is easy. However, soon you will face challenges of having multiple tables, the relationship between tables, the direction of relationship, active or inactive relationship and so on. You also soon realize that you need a proper data model called star-schema which is combination of fact and dimension tables. But, wait a second, you never learned all these fundamentals. What...
Drillthrough Paginated Reports

September 25, 2021 / No Comments »

I'm helping a client convert a few SSRS reports from SharePoint to Power BI Premium Per User (PPU). SSRS is of course near and dear to my heart because of all the work I've done around it circa 2004-2010 (yep, it's been that long), books, MVP awards, etc. Since its humble beginnings, SSRS have had a solid architecture that excelled in extensibility. You'd be hardly pressed to face a requirement that couldn't meet with SSRS back then. Unfortunately, most of these extensibility features, such as custom assemblies, custom security, custom delivery extensions, custom renderers (essentially everything related to custom code) didn't make it to paginated reports in Power BI Premium. Not many companies are using these features, so they probably won't be a showstopper for your migration. To their credit, Microsoft is closing the gap between SSRS and paginated reports. As of now, the feature limits that you might run...
Chasing SSAS Connection Timeouts

August 23, 2021 / No Comments »

Suppose you have a Tabular model and you send a massive DAX query to it that could run for hours, such as to calculate many measures (in our case hundreds) for each customer overnight so that you can cache the results and delight the user with super-fast lookups. This issue could also apply to Multidimensional although in this case Tabular was used. The server times out sporadically the query after a random execution time. You have changed all possible connection timeout options (SSAS ServerTimeout, SSIS connection timeout, etc.) to no avail. In fact, if you have scheduled an Agent job that calls an SSIS package that executes the query, the package doesn't register the exception and continues executing indefinitely, but a Profiler trace (or XEvents session) shows that the server raises a Connection Timeout error. How to fix this horrible issue? Change the two undocumented settings in the MSMDSRV.INI file...
Azure Data Factory is Getting Better All the Time

August 21, 2021 / No Comments »

Three years ago, I wrote that it would probably take a decade for to mature and close the gap with SSIS. To its credit thought, while it's still lagging in the area of extensibility, ADF added features that we don't have in SSIS so I'm developing a taste for it: Schema drift – Suppose you want to automatically stage new columns as they added to a source table. Or, columns might be deleted from the source but your ETL shouldn't fail. You can't do these things with SSIS which is tightly coupled with the data source schema. ADF data flows, however, can handle this. Parallel loops – Want to loop through some tables but load them in parallel? The ADF ForEach loop can be parallelized up to 50 concurrent threads. Source partitioning – Let's say you have a big source table and you want to speed up staging. You can...
Atlanta MS BI and Power BI Group Meeting on August 2nd

July 25, 2021 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, August 2nd, at 6:30 PM. Avi Singh (LearnPowerBI.com) will discuss how you can achieve a successful Power BI career. For more details and sign up, visit our group page. Presentation: How to Create a Successful Power BI Career Without the Struggle (By "Niching Down") Date: August 2nd Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: Your Grandma was right! You cannot please everyone. And when you go out there and wave your flag as a Power BI Professional, that's exactly what you are trying to do. And it doesn't work. Either you get no results or have to work really hard for every inch of progress. The problem is that most professionals either miss or mess-up the first crucial step of their Power BI Career - Niching...