Blog – Page 13 – Prologika

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...
Interactive Excel Pivots in Power BI

July 19, 2021 / No Comments »

A long-standing limitation of Power BI has been that Excel pivot reports connected to external Analysis Services models can't be interacted with when uploaded to Power BI Service. You get the cached pivot report but any attempt to interact with the report online (that is in Excel Online), such as to change a slicer or filter, would result in an error complaining that the connection can't be refreshed and there hasn't been a workaround. Today, Microsoft partially lifted this limitation by supporting interactive pivots connected to Power BI datasets. Unfortunately, pivots connected to external Analysis Services models (both on-prem and cloud) still don't support interactivity. For some reason, this feature is called "connected PivotTable refresh" although a better name would be "interactive Excel pivots finally". It works in Power BI Pro and Premium. Unlike the initial announcement that speculated that this feature would be available by simply uploading your Excel...
Cloud Inhibitors

July 11, 2021 / No Comments »

In a previous blog, I said I consider it a travesty when a vendor's cloud offering prohibits direct access to the data in its native storage, which is typically a relational database, and therefore a perfect store for data analytics and integration, such as to run real-time analytics or export the data to load a data mart. There is nothing simpler than connecting to a relational database, which is designed to handle efficiently select and filter operations. Unfortunately, it's becoming a norm where the move to the cloud requires going through all sorts of hoops to access the data. Some enlightened providers offer direct access to the native store by charging a premium fee. However, this is not usually an option and most vendors require you call their REST APIs. Since, the move to the cloud shouldn't make things more difficult, I call these vendors "cloud inhibitors". Such vendors usually...
Synapse Serverless: The Good, The Bad, and The Ugly

June 29, 2021 / No Comments »

When you a provision a Synapse workspace, you get a serverless endpoint for free (or almost free). This endpoint represents Synapse Serverless: a query service for ad-hoc exploration of data in CVS, Parquet, and JSON files stored in Azure Data Lake. The Good Being able to query files using SQL is great. Some of you might remember the U-SQL language that was introduced a few years ago alongside Azure Data Lake Storage (ADLS) Gen 1 which is now deprecated. It never caught up because it looked like SQL but it wasn't (it was actually closer to C#). Now we're talking about real SQL. To query files! This opens the possibility to implement a logical warehouse (the emphasis is on logical as everyone to my knowledge who tried to replace a data warehouse with a data lake has failed). Or, you can connect Power BI to the serverless endpoint and start...
Atlanta MS BI and Power BI Group Meeting on July 5th

June 27, 2021 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, July 5th, at 6:30 PM. Reid Havens (Consultant and MVP) will show us how to get the most out of Power BI bookmarks and buttons. And I'll cover the Power BI latest enhancements. For more details and sign up, visit our group page. Presentation: Bedazzling Your Bookmarks & Buttons Date: July 5th Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: Bookmarks and Interactive Buttons are two of the best ways to add web-like user experiences into Power BI reports. Bookmarks help create a more seamless filter and navigation experience. Buttons can be used with bookmarks, but can also be leveraged for a lot of other report functionality and navigation. Speaker: Reid Havens is the founder of Havens Consulting Inc. and is a Microsoft Most Valued Professional (MVP)....