Blog – Page 5 – Prologika

When All You Have is a Hammer… (Dbt Tool for ETL)

October 16, 2023 / No Comments »

Someone asked the other day for my opinion about the open-source dbt tool for ETL. I hadn't heard about it. Next thing I've noticed was that Fabric Warehouse added support for it, so I got inspired to take a first look. Seems like an ELT-oriented tool. Good, I'm a big fan of the ELT pattern whose virtues I extolled many times here. But a Python-based tool that requires writing templates in a dev environment, such as Visual Studio Code? Yuck! So, what's my first impression? Same thoughts as when I see developers use another generic programming language, such as C#, for ETL. You can do it but why? For years, I've been trying to get developers out of custom coding for ETL to low-code ETL specialized tools, such as SSIS and ADF. Just because you studied Python in college, should you use Python for everything? I guess the open-source custom...
Atlanta Microsoft BI Group Meeting on October 2nd (30 Power BI Tips and Tricks)

September 26, 2023 / No Comments »

Atlanta BI fans, please join us for the next meeting on Monday, October 2nd, at 6:30 PM ET. Michael Hewitt will share 30 tips to make you more productive with everything Power BI. Your humble correspondent will help you catch up on Microsoft BI latest. 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: 30 Power BI Tips and Tricks Delivery: In-person Date: October 2nd Time: 18:30 – 20:30 ET Level: Beginner to Intermediate 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 Venue Improving Office 11675 Rainwater...
Hiking the Precipice Trail

September 22, 2023 / No Comments »

One of trails my wife and I hiked today in the Acadia National Park was the Precipice Trail. This trail is considered one of the hardest trails in the United States. In fact, "hiking" is a misnomer as most of the ascent involves vertical rock climbing and pulling yourself up on iron rangs. Slipping and falling can surely result in a serious injury or death as there are no safety nets and there were such incidents in the past. However, if you put yourself in the right mindset and prepare, you probably won't find it that hard. In fact, we've found the descent more challenging than the ascent which we thought was exhilarating. And the Gulf of Main views were breathtaking! How many ascents and views do we miss in life by not taking moderate risks?
Dissolving Partnerships

September 17, 2023 / No Comments »

I've been an independent consultant for almost 20 years and through most of this time, my company (Prologika) has been a Microsoft consultancy partner with Gold Data Analytics and Gold Data Platform competencies. Speaking of which, I'd like to thank all the wonderful people who have helped Prologika attain and maintain these competencies over the years by taking rigorous certification exams. I couldn't have done it without you! My simple definition of a consultant is an external expert whose services improve the client's situation. Therefore, the most important criterion for measuring the consultant's effectiveness is how much "lift" the client gets. Effectiveness is rarely tangible because it's difficult to measure. However, in the past, the second component (besides certifications) for maintaining the Microsoft partnership was providing customer references to Microsoft. I don't think Microsoft ever called the customers but if they did, a simple question would have been "On the...
Excel Connected Tables: Another Opportunity Missed

September 11, 2023 / No Comments »

For years users have been asking for Excel tables (not pivots) connected to SSAS so they can simply browse the data in a tabular format not subjected to the shortcomings of pivots. And for years Excel has been supporting just pivots that natively understand only SSAS OLAP cubes. Is Power BI changing things? Somewhat. Enter refined Excel connected tables. They look great on the surface. You go through a wizard that lets you select and filter your data and you get an Excel table connected to a Power BI dataset. Awesome, right? Unfortunately, what's missing is the Field List. To make the feature useful, the user should be able to access the Field List and remove, add, filter, as they can do with pivots. However, once the table is generated, to change it you either must go through the wizard again or change the DAX query (right-click the table, click...
SaaS Winners – InTempo Software

September 6, 2023 / No Comments »

I ranted many times about SaaS vendors preventing direct access to data in its native storage, which is typically a relational database. It's sort of like going to a bank, handing them over your money, and they telling you that you can only get your money back in cash, in Bitcoin, in ₿100 bills but no more than ₿1,000 per withdrawal, or whatever madness suits the bank best. I wonder how long this bank will be in business. Yet, many companies just hand over the keys of the data kingdom to a SaaS vendor and they wonder how to get their "precious" back so they can build EDW and enable modern analytics. Since when direct access became an anathema perpetuated by mega software vendors, including Microsoft? I'll repeat again that there are no engineering reasons to prevent direct access to your data, even if you must pay more to "isolate"...
Atlanta Microsoft BI Group Meeting on September 11th (Introducing Lakehouse in Microsoft Fabric)

September 5, 2023 / No Comments »

Atlanta BI fans, please join us for the next meeting on Monday, September 11th, at 6:30 PM ET. Shabnam Waston (BI Consultant and Microsoft MVP) will introduce us to the Lakehouse engine in Microsoft Fabric. Shabnam will also sponsor the meeting. Your humble correspondent will help you catch up on Microsoft BI latest. 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 Lakehouse in Microsoft Fabric Delivery: Onsite Date: September 11th Time: 18:30 – 20:30 ET Level: Beginner to Intermediate 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...
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,...