Blog – Page 9 – Prologika

Batch Renaming Columns in Power BI Tables

September 16, 2022 / No Comments »

Scenario: You need to rename many columns in a Power BI table. Usually, I rename measure-related columns with "Base" suffix and hide them, and then I create explicit measures. Since renaming one column at the time is no fun, you're looking for an automated solution. Having heard about the Tabular Editor scripting feature, you're tempted to whip out a script like this: You select all columns that must be renamed, run the script, and it appears to work (make sure to enable the Power BI experimental feature in the Tabular Editor Preferences before you run the script since otherwise nothing will happen)! You save the changes, the columns show up with the new names in Power BI Desktop, and you're about to call it a successful day. But then when you refresh the table, you get greeted with an error that the column already exists and can't be added, and...
Refreshing Power BI Datasets from SSIS

September 5, 2022 / No Comments »

Scenario: You use SSIS to load data for on-prem BI solution. As a last step of the ETL pipeline, you want to refresh a Power BI dataset. There's quite a bit of misinformation on the Internet about how to do this, hence this blog. Solution: If the dataset is hosted in Power BI Pro workspace, the only way is to use the Power BI REST APIs and there are some good examples out there using PowerShell or Microsoft Automate flows. However, if the dataset is hosted in a Premium-per-user (PPU) or Premium workspace, you can use the SSIS built-in Analysis Services Processing Task. And, no, you don't need third-party components. An unattended process can authenticate against Power BI using either a Power BI-licensed account or service principal. I couldn't get service principals to work with Power BI datasets. More than likely, this is because the service principal needs to be...
Atlanta MS BI and Power BI Group Meeting on September 12th (Incremental Refresh and Hybrid Tables in Power BI)

September 5, 2022 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, September 12th, at 6:30 PM ET. Shabnam Watson (Microsoft MVP) will discuss how to reduce the dataset refresh time with incremental refresh policies and how to implement tables with hybrid storage. For more details and sign up, visit our group page. Presentation: Incremental Refresh and Hybrid Tables in Power BI Date: September 12th Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: Incremental Refresh makes it possible for Power BI to handle large datasets by partitioning the data into segments and making refreshes faster, more reliable, and less resource intensive. Hybrid tables, a new addition, can be easily configured for a table with Incremental Refresh to enable different storage modes for its different partitions. This allows historical data to load into Power BI’s memory (import) for super-fast...
Not Your Kimball’s DW

August 13, 2022 / No Comments »

BI practitioners, myself included, have been following the Kimbal tenets for dimensional schema design for years. In fact, it's a second nature to dimensionalize every BI project the "proper" way. You identify fact tables, grain, dimensions, etc. Dimensions of course would have surrogate keys – your insurance against weird things happening down the road, such as tracking Type 2 changes, unreliable business keys, acquisitions, and what not. Of course, there are downsides. A lot of time is spent on the dimension design – regular dimensions, junk dimensions, mini dimensions... Your ETL process must load these dimensions. And there are dependencies. You can't just truncate and reload a dimension because the surrogate keys in the fact tables will be invalidated. But here comes a project that throws everything off. Don't you love projects like these? I'm designing a data warehouse solution for small financial company, where I model financial loans. The...
Atlanta MS BI and Power BI Group Meeting on August 1st (Power BI Automation)

July 25, 2022 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, August 1st, at 6:30 PM ET. We'll have two presentations by 3Cloud. For more details and sign up, visit our group page. Presentation: 1. “Power BI Meets Programmability – TOM, XMLA, and C#” by Kristyna Hughes 2. “Automation Using Tabular Editor Advanced Scripting” by Tim Keeler Date: August 1st Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: Power BI Meets Programmability – TOM, XMLA, and C# Tune in to learn how to programmatically add columns and measures to Power BI data models using TOM, XMLA, and C#! XMLA is a powerful tool available in the online Power BI service that allows report developers to connect to their data model and adjust a variety of entities outside the Power BI Desktop application. Combined with a .NET application, this...
Datasets vs Datamarts

July 18, 2022 / No Comments »

I sent a proposal for implementing a classic BI solution: Azure SQL-based datamart (not Power BI datamart please), ETL, semantic model, and reports. The client had a sticker shock. Return to sender … as other BI companies that quoted can do it for half! Upon digging, it turned out the other companies would build the semantic model (aka Power BI dataset) directly on top of the data source. On a Time&Materials (T&M) basis and charge by the hour, of course, what else? By contrast, I give fixed-price milestone-driven proposals and I don't get paid unless I deliver and meet written and agreed upon success criteria, but that's a different story. So, let me count the ways as the poet would say. It's certainly technically possible to slap a dataset on top of the data source(s). That's what self-service BI is all about right … until it doesn't serve anymore. Check...
Atlanta MS BI and Power BI Group Meeting on July 11th (Pushing the Query Folding limits with Power Query)

July 2, 2022 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, July 11th, at 6:30 PM ET. For more details and sign up, visit our group page. Presentation: Pushing the Query Folding limits with Power Query Date: July 11th Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: One of Power Query’s most powerful features is its ability to translate the Power Query formula language (M) back to a source systems native language and in this session, we’ll push the limits and possibilities to avoid “breaking the fold” and explore some potential dark magic with List functions. A base understanding of T-SQL is helpful though not required for this session. Speaker: From financial services to felines, the World Wide Web to professional wrestling - Alex Powers has an affinity for the conventional and unconventional when it comes to information....
Power BI Field Parameters

June 7, 2022 / No Comments »

Coming back from a long vacation and I almost missed this new Power BI killer feature: Field Parameters! Not to be confused with Dynamic M Query Parameters that I ranted about here, field parameters solve a long-standing limitation of Power BI that prevents binding dynamically fields to a visual. Dynamic binding isn't an issue with measures because they are dynamic and can evaluate runtime conditions, such as slicer selection, but dimensions are a different story. Once they are bound to a category bucket in a visual, you couldn't change them on the fly. Yet, one common scenario was to let the user control which fields will be used for slicing the measure(s) in a visual. I've seen rather convoluted implementations to get around this limitation. Field parameters to the rescue. Now once you create a field parameter and bind it to the visual, the user can simply select which field...
Power BI Datamarts: the Good, the Bad, and the Ugly

June 6, 2022 / No Comments »

As Microsoft announced here, Power BI datamarts are upon us. I can almost see an important enterprise client demanding "self-service datamarts me now or else… ", thus inspiring an opportunity for another premium feature, spearheaded with great vision and effort, but questionable practical value. In a nutshell, a Power BI datamart is a combo of Power BI Premium and a Microsoft-hosted Azure SQL Database aiming to simplify the implementation of a departmental datamart. The Good Unlike other vendors, such as Domo and their proprietary and overly expensive stack, Microsoft has decided to go with somewhat open solution consisting of tools that Power BI users already know: Power Query, Power BI Desktop (for the first time some of its modeling features, such as relationships and DAX measures, made it to the cloud), and SQL Server. Microsoft provisions the database for you although surrounds it with some red tape (more on this...
A Case for Azure Analysis Services

June 5, 2022 / No Comments »

Microsoft BI practitioners have three options for hosting semantic models: SSAS (on prem), Azure Analysis Services (cloud), and Power BI (cloud). AAS is somewhat caught between a rock and a hard place. Given that Power BI gets the most attention for cloud deployment, why would you consider AAS at all? There are two main reasons: Cost – Organizational semantic models might require a lot of memory and crunching power. Hosting them on AAS might be more cost effective. For example, AAS S4 runs at around $5,000 which at the same price point as Power BI Premium P1. However, it gives you 100 GB of RAM and 20 cores, whereas P1 has only 25 GB and 8 cores. Scaling out – A feature unique to AAS is ability to scale out to multiple query replicas. This is not an option with Power BI Premium, and it requires quite a bit of...