Blog – Prologika

Temporal Tables

November 10, 2024 / No Comments »

I like SQL Server temporal tables for implementing ODS-style tables and change data tracking (CDS) for three main reasons: SQL Server maintains the system versioning. By contrast, I have witnessed erroneous Start/End dates for pretty much all home grown implementation. Further, SQL Server grains the changes at millisecond level. There is a clean separation between the current state of data and historical data. SQL Server separates the historical changes to a history table. You can establish a flexible data retention policy. A retention policy can be established at database or table level. SQL Server take care of purging the expired data. At the same time, temporal tables are somewhat more difficult to work with. For example, you must disable system versioning before you alter the table. Here is the recommended approach for altering the schema by the documentation: BEGIN TRANSACTION ALTER TABLE [dbo].[CompanyLocation] SET (SYSTEM_VERSIONING = OFF); ALTER TABLE [CompanyLocation]...
Atlanta Microsoft BI Group Meeting on November 4th (Accelerating your Fabric Data Estate with AI & Copilot)

October 29, 2024 / No Comments »

Atlanta BI fans, please join us in person for our next meeting on Monday, November 4th at 6:30 PM ET. Stacey Jones (Principal Data & AI Cross-Solution Architect at Microsoft) and Elayne Jones (Solutions Architect at Coca-Cola Bottlers Sales and Services) will explore the AI and Copilot capabilities within Microsoft Fabrics. And I'll help you catch up on Microsoft BI latest. I will sponsor the event which marks the 14th anniversary of the Atlanta Microsoft BI Group! For more details and sign up, visit our group page. Details Presentation: Accelerating your Fabric Data Estate with AI & Copilot Delivery: In-person Date: November 4th, 2024 Time: 18:30 – 20:30 ET Level: Beginner to Intermediate Food: Pizza and drinks will be provided 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 Dr Suite #100 Alpharetta, GA 30009 Overview: In this...
Implementing Role-playing Dimensions in Power BI

October 11, 2024 / No Comments »

Role-playing dimensions are a popular business requirement but yet challenging to implement in Power BI (and Tabular) due to a long-standing limitation that two tables can’t be joined multiple times with active relationships. Declarative relationships are both a blessing and a curse and, in this case, we are confronted with their limitations. Had Power BI allowed multiple relationships, the user must be prompted which path to take. Interestingly, a long time ago Microsoft considered a user interface for the prompting but dropped the idea for unknown reasons. Given the existing technology limitations, you have two implementation choices for implementing subsequent role-playing dimensions: duplicating the dimension table (either in DW or semantic model) or denormalizing the dimension fields into the fact table. The following table presents pros and cons of each option: Option Pros Cons Duplicate dimension table in semantic model or DW No or minimum impact on ETL Minimum...
Generative AI: Excessive Reinforcement Learning

August 28, 2024 / No Comments »

Do you know that without human intervention the Generative AI responses would be too toxic after training the model from the garbage on Internet? Do you know that OpenAI hired Kenyan workers to make ChatGPT less toxic? To throw in some jargon, this is called RLHF (reinforcement learning with human feedback). Not sure who Microsoft hired to cleanse copilots, but apparently they went overboard. Here is a Windows Copilot jerk-knee response that I got to a prompt asking when to open with 2 No Trump in contract bridge. I see, Generative AI refuse to generate since "trump" is a taboo topic nowadays. AI dummy!
Fabric Direct Lake: Memory Utilization with Interactive Operations

August 15, 2024 / No Comments »

As I mentioned in my Power BI and Fabric Capacities: Thinking Outside the Box, memory limits of Fabric capacities could be rather restrictive for large semantic models with imported data. One relatively new option to combat out-of-memory scenarios that deserves to be evaluated and added to the list if Fabric is in your future is semantic models configured for Direct Lake storage. The blog covers results of limited testing that I did comparing side by side the memory utilization of two identical semantic models with the first one configured to import data and the second to use Direct Lake storage. If you need a Direct Lake primer, Chris Webb has done a great job covering its essentials here and here. As a disclaimer, the emphasis is on limited as these results reflect my personal observations based on some isolated tests I’ve done lately. Your results may and probably will vary...
Fabric Capacity Limits

August 14, 2024 / No Comments »

Here is table that is getting more and more difficult to find as searching for Fabric capacity limits returns results about CU compute units (for the most part meaningless in my opinion). I embed in a searchable format below before it vanishes on Internet. The most important column for semantic modeling is the max memory which denotes the upper limit of memory Fabric will grant a semantic model. SKU Max memory (GB)1, 2 Max concurrent DirectQuery connections (per semantic model)1 Max DirectQuery parallelism3 Live connection (per second)1 Max memory per query (GB)1 Model refresh parallelism Direct Lake rows per table (in millions)1, 4 Max Direct Lake model size on OneLake (GB)1, 4 F2 3 5 1 2 1 1 300 10 F4 3 5 1 2 1 2 300 10 F8 3 10 1 3.75 1 5 300 10 F16 5 10 1 7.5 2 10 300 20 F32 10...
Atlanta Microsoft BI Group Meeting on September 3rd (Create Code Copilots with Large Language Models)

August 12, 2024 / No Comments »

Atlanta BI fans, please join us in person for the next meeting on Monday, September 3th at 6:30 PM ET. Your humble correspondent will show you how to use Large Language Models, such as ChatGPT, to create your own copilots for Text2SQL and Text2DAX. I'll also help you catch up on Microsoft BI latest. I will sponsor the event which marks the 14th anniversary of the Atlanta Microsoft BI Group! For more details and sign up, visit our group page. Details Presentation: Create Code Copilots with Large Language Models Delivery: In-person Date: September 3rd, 2024 Time: 18:30 – 20:30 ET Level: Beginner to Intermediate Food: Pizza and drinks will be provided 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 Dr Suite #100 Alpharetta, GA 30009 Overview: Resistance is futile! Instead of fearing that AI...
Power BI and Fabric Capacities: Thinking Outside the Box

August 3, 2024 / No Comments »

I’m conducting an assessment for a client facing memory pressure in Power BI Premium. You know these pesky out of memory issues when refreshing a biggish dataset. They started with P1, moved to P2, and now are on P3 but still more memory is needed. The runtime memory footprint of the problematic semantic model with imported data is 45 GB and they’ve done their best to optimize it. Since its beginning, Power BI Pro per-user licensing (and later Premium Per User (PPU) licensing) has been very attractive. Many organizations with a limited number of report users flocked to Power BI to save cost. However, organizations with more BI consumers gravitated toward premium licensing where they could have unlimited number of report readers against a fixed monthly fee starting at listed price of $5,000/mo for P1. Sounds like a great deal, right? I must admit that I detest the premium licensing...
Atlanta Microsoft BI Group Meeting on August 5th (Elevate Program Management with Power BI & DevOps)

July 30, 2024 / No Comments »

Atlanta BI fans, please join us in person for the next meeting on Monday, August 5th at 6:30 PM ET. Elayne Jones and Matt Kim (Solutions Architects at Coca-Cola) will show us how to bring Azure DevOps data to life by creating data models and interactive reports in Power BI. Your humble correspondent will help you catch up on Microsoft BI latest. CloudStaff.ai will sponsor the event. For more details and sign up, visit our group page. Details Presentation: Elevate Program Management with Power BI & DevOps Delivery: In-person Date: August 5, 2024 Time: 18:30 – 20:30 ET Level: Intermediate Food: Pizza and drinks will be provided 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 Dr Suite #100 Alpharetta, GA 30009 Overview: Have you ever opened Azure DevOps and felt overwhelmed by the vast sea of program...
LLM Adventures: RAG Apps

July 19, 2024 / No Comments »

This post summarizes my research around the increasingly popular RAG apps, and it’s meant more as an internal memo to myself to summarize existing findings should one day a suitable project comes along. However, someone starting with RAG development might find this useful (we are all LLM rookies). RAG is a fascinating topic and presents another great case for generative AI in data analytics. RAG (retrieval-augmented generation) apps apply AI to let end users intelligently search data, such as PDF or Word documents, using natural questions. The most common scenario is for searching internal data because public LLM models don’t have access to your corporate data repositories and therefore know nothing about your data. Suppose your HR department has accumulated a large knowledge base of files detailing internal policies, such as health plans. Using a home-grown RAG app, the user can type natural questions, such as “Which plan supports vision?”...