Blog – Page 5 – Prologika

Yet Another Currency Conversion Pattern

September 1, 2023 / No Comments »

Currency conversion is a common requirement, such as when implementing analytics on top of ERP sales data recorded in multiple currencies. The most flexible approach is to allow the user to select a currency and perform the conversion at runtime, ideally by centralizing the conversion code in Power BI calculation groups. However, this approach has several shortcomings: Based on my experience, the most common requirement is to convert to a single corporate currency, such as USD, but provide the foreign subsidiaries an option to see the data in their local currency, such as for testing that the DW data is accurate. Therefore, the user should be presented with a slicer (or filter) with only two options: USD and Local. In the case of USD, all monetary measures will be converted to USD. In the case of Local, no conversion is needed. Centralizing the code in a calculation group won't work...
Improving Data Quality

August 22, 2023 / No Comments »

Poor data quality is one of the most challenging hurdles for successful data analytics. The story typically goes like this. The client has a flexible line of business system that people tend to use and abuse, such as entering garbage in free-form text fields that shouldn't be free-form to start with. There is no data stewardship or data entry oversight. I'm not blaming anyone because people go about their business trying to get something done as fast as possible and BI is not on their minds. The issue is further complicated by acquisitions that usually bring more systems and more garbage. You and I've been there…the bigger and more complex the organization, the bigger the mess. How do we solve this challenge? Should we buy a super-duper master data management (MDM) system to solve the data quality mess? Or should we kick the can down the road and tackle data...
Top 5 Lessons Learned from IBM Db2 Integration Projects

August 12, 2023 / No Comments »

I've done a few BI integration projects extracting data from ERPs running on IBM Db2. Most of the implementations would use a hybrid architecture where the ERP would be running on an on-prem mainframe while the data was loaded in Microsoft Azure. Here are a few tips if you're facing this challenge: IBM provides JDBC, ODBC, and OLE DB drivers. The JDBC driver is not applicable to the Microsoft toolset because none of the Microsoft BI tools run on Java runtime. Microsoft provides an OLE DB Provider for Db2. If you use Azure Data Factory or Power BI Desktop, you can use the bundled Microsoft OLE DB driver for Db2. Here are the Azure Data Factory settings for setting up a linked service using the Db2 driver: Server name – specify the server name of IP address of the IBM Db2 server. For DB2 LUW the default port is 50000,...
Fixing ADF DataFactoryPropertyUpdateNotSupported Deployment Error

August 2, 2023 / No Comments »

This took a while to figure out…so someone might find it useful. Scenario: You have set up two Azure Data Factory instances for DEV and PROD environment. The DEV environment uses a self-hosted integration runtime to read data from on-prem data sources. The day has come to deploy your masterpiece to PROD. You share the DEV self-hosted runtime with the production ADF instance. You export the ADF ARM template and attempt to run it against the pristine PROD environment. You're greeted with DataFactoryPropertyUpdateNotSupported error. The "property" here is linked self-hosted runtime. Solution: The following solution worked for me. In your ARM template, scroll all the way to the bottom and add the following to the typeProperties element of the self-hosted runtime: IMPORTANT: Make sure that in your production ADF, you have created a shared self-hosted runtime that points to the one in your DEV environment before you proceed with the...
Atlanta Microsoft BI Group Meeting on August 7th (Introducing Microsoft Fabric)

August 1, 2023 / No Comments »

Atlanta BI fans, please join us for the next meeting on Monday, August 7th, at 6:30 PM ET. James Serra (Data & AI Solution Architect at Microsoft) will join us remotely to introduce us to Microsoft Fabric. For more details and sign up, visit our group page. PLEASE NOTE A CHANGE TO OUR MEETING POLICY. WE HAVE DISCONTINUED ONLINE MEETINGS VIA TEAMS. THIS GROUP MEETS ONLY IN PERSON. WE WON’T RECORD MEETINGS ANYMORE. THEREFORE, AS DURING THE PRE-PANDEMIC TIMES, PLEASE RSVP AND ATTEND IN PERSON IF YOU ARE INTERESTED IN THIS MEETING. Presentation: Introducing Microsoft Fabric Delivery: Speaker will join us remotely via Teams Date: August 7th Time: 18:30 – 20:30 ET Level: Beginner Food: Sponsor wanted Agenda: 18:15-18:30 Registration and networking 18:30-19:00 Organizer and sponsor time (events, Power BI latest, sponsor marketing) 19:00-20:15 Main presentation 20:15-20:30 Q&A ONSITE Improving Office 11675 Rainwater Dr Suite #100 Alpharetta, GA 30009...
A First Look at Microsoft Fabric: Recap

July 30, 2023 / No Comments »

Did I disappoint you? Or leave a bad taste in your mouth? You act like you never had love And you want me to go without U2 In previous posts, I shared my initial impression of the recently announced Microsoft Fabric and its main engines. Now that we have the Fabric licensing and pricing, I'm ready to wrap up my review with a few parting notes. Here is how I plan to position Fabric to my clients: Enterprise clients These clients have complex data integration needs. More than likely, they are already on a Power BI Premium contract and highly-discounted pricing model that is reviewed and renewed annually with Microsoft. Given that Fabric can be enabled on premium capacities, you should definitely consider it selectively when it makes sense. For now, I believe a good case can be made for data lake and lakehouse if that's your thing. Now you...
Fabric Semantic Modeling: The Good, the Bad, and the Ugly

July 14, 2023 / No Comments »

In retrospect, I'd say I owe 50% of my BI career to Analysis Services and its flavors: Multidimensional, Tabular, and later Power BI. This is why I closely follow how this technology evolves. Fast forwarding to Fabric, there are no dramatic changes. Unlike the other two Fabric Engines (Lakehouse and Warehouse), Power BI datasets haven't embraced the delta lake file format to store its data yet. The most significant change is the introduction of a new Direct Lake data access mode alongside the existing Import and DirectQuery. The Good Direct Lake will surely enable interesting scenarios, such as real-time BI on top of streaming data. It requires Parquet delta lake files and therefore it's available only when connecting to the Lakehouse managed area (Tables folder) and Warehouse tables. Given that Parquet is a columnar format, which is what Tabular VertiPaq is, basically Microsoft has changed the engine to read Parquet...
Power BI Projects and Git Integration: The Good, the Bad, and the Ugly

July 9, 2023 / No Comments »

Is it getting better? Or do you feel the same? Will it make it easier on you now? You got someone to blame U2 Likely influenced by the Gartner's Fabric vision, Microsoft Fabric is eclipsing all things Power BI nowadays to the extent of replacing the strong Power BI brand and logo with Fabric's. Now that the red star Synapse has imploded into a black hole, Fabric has taken its place and it's engulfing everything in its path. But to digress from Fabric, let's take a look at two developer-oriented and frequently requested features that fortunately doesn't require Fabric: Power BI Desktop projects and workspace integration with Git. The video in the link is a good starting point to understand how these features work. Basically, the first feature lets you save a Power BI Desktop file as a *.pbip file which generates a set of folders with human-readable text files,...
Fabric Data Integration: The Good, the Bad, and the Ugly

July 8, 2023 / No Comments »

Oops, I did it again I played with your heart Got lost in the game… Britney Spears In previous posts, I shared my thoughts about Fabric OneLake, Lakehouse, and Data Warehouse. They are of course useless if there is no way to get data in and out of Fabric. Data integration and data quality is usually the most difficult part of implementing a BI solution, accounting for 60-80% of the overall effort. Therefore, this post is about Fabric data integration options. Fabric supports three options for automated data integration: Data Pipeline (Azure Data Factory pipeline), Dataflow Gen2 (Power BI dataflow), and Notebook (Spark). I summarize these three options in the following table, which loosely resembles the Microsoft comparison table but with my take on it. Data pipeline (ADF pipeline/copy activity) Dataflow Gen2 (Power BI dataflow) Notebook (Spark) Primary user BI developer Business analyst Data scientist, Developer Patterns supported ETL/ELT ETL...
Atlanta Microsoft BI Group Meeting on July 11th (Azure OpenAI – Answers to Your Natural Language Questions)

July 5, 2023 / No Comments »

Atlanta BI fans, please join us for the next meeting on Tuesday, July 11th, at 6:30 PM ET. Stacey Jones (Cross Solution Architect at Microsoft) will join us in person to present OpenAI and Copilot. Your humble correspondent will demo the newly released PBI Desktop project format. For more details and sign up, visit our group page. PLEASE NOTE A CHANGE TO OUR MEETING POLICY. EFFECTIVE IMMEDIATELY, WE ARE DISCONTINUING ONLINE MEETINGS VIA TEAMS. THIS GROUP MEETS ONLY IN PERSON. WE WON’T RECORD MEETINGS ANYMORE. THEREFORE, AS DURING THE PRE-PANDEMIC TIMES, PLEASE RSVP AND ATTEND IN PERSON IF YOU ARE INTERESTED IN THIS MEETING. Presentation: Azure OpenAI - Answers to Your Natural Language Questions Date: July 11th (Please note that because of the July 4th holiday, this meeting is on Tuesday) Time: 18:30 – 20:30 ET Level: Intermediate Food: As of now, food won’t be available for this meeting. We...