Power BI Workspace Identity Authentication

/0 Comments/in /by

What credentials do you use to refresh your Power BI semantic models from Azure SQL SKUs, such as Azure SQL Database. Probably your credentials or a designated Entra account? Both are not ideal for a variety of reasons, including requiring a password. More advanced users might be using service principals, which are more secure but require secret renewal after a maximum of 24 months, which is a hustle.

Somewhere along the way without me noticing, Microsoft added a better authentication option for refreshing Power BI semantic models: workspace identity. This option lets the Power BI workspace using its own managed identity to authenticate to the data source. And it’s available in all Power BI and Fabric SKUs!

What’s not clear from the documentation is how to grant permissions to the workspace identity to read data from Azure SQL SKUs but it’s no different that granting access to the Azure Data Factory managed identity.

  1. Create the workspace identity as explained in the documentation. It has to be done for each workspace that has your published model(s).
  2. In SSMS, connect to your Azure SQL Database using Entra credentials that has permissions to manage security (SQL login won’t work).
  3. Open a new query connected to your database.
  4. Execute the following script assuming you want to grant read permissions to the workspace identity:
CREATE USER [<workspace name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<workspace name>];

Then back to Power BI, configure your semantic model for workspace identity authentication:

  1. Navigate to the semantic model settings and click “Edit credentials”.
    A screenshot of a computer AI-generated content may be incorrect.
  2. Select “Workspace identity” as the authentication method.

A screenshot of a computer AI-generated content may be incorrect.

That’s it. Using the workspace identity to read data during model refresh is more secure and easier to manage.

 

Atlanta Microsoft BI Group Meeting on December 1st (Migrating Semantic Models to Fabric Direct Lake)

/0 Comments/in , /by

Atlanta BI fans, please join us in person for our next meeting on Monday, December 1st at 18:30 ET. I’ll show you how to Fabric DirectLake semantic models can help you tackle long refresh cycles and scalability headaches. And your humble correspondent will walk you through some of the latest Power BI and Fabric enhancements. Improving will sponsor the meeting. For more details and sign up, visit our group page.

Delivery: In-person
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 (news, Power BI latest, sponsor marketing)
19:00-20:15 Main presentation
20:15-20:30 Q&A

Overview: Are your Power BI semantic models hitting memory limits? Tired of bending backwards to mitigate long refresh cycles and scalability headaches? Join me for a deep dive into Fabric Direct Lake — a game-changing feature that can help enterprise customers eliminate refreshes, lower licensing cost, and work with production-scale data instantly.

You’ll learn:
-Why Direct Lake is a breakthrough for large semantic models
-How to migrate from Import mode with real-world tools and strategies
-Common pitfalls and how to avoid them
-Performance insights and practical tips from actual project

Bonus: See how AI tools like Grok, Copilot or ChatGPT can streamline your migration process!

Whether you’re a BI pro, data engineer, or decision-maker, this session will equip you with the knowledge to scale smarter, design better, and deliver faster.

Speaker: Teo Lachev is a consultant, author, and mentor, with a focus on Microsoft BI. Through his Atlanta-based company Prologika (a Microsoft Gold Partner in Data Analytics and Data Platform) he designs and implements innovative solutions that bring tremendous value to his clients. Teo has authored and co-authored several books, and he has been leading the Atlanta Microsoft Business Intelligence group since he founded it in 2010. Microsoft has recognized Teo’s contributions to the community by awarding him the prestigious Microsoft Most Valuable Professional (MVP) Data Platform status for 15 years. Microsoft selected Teo as one of only 30 FastTrack Solution Architects for Power BI worldwide.

Sponsor: Prologika (https://prologika.com) helps organizations of all sizes to make sense of data by delivering tailored BI solutions that drive actionable insights and maximize ROI. Your BI project will be your best investment!

Presentation Slides

PowerBILogo

SaaS Losers and Winners: Paylocity and Element

/0 Comments/in /by

“We are sailing to Philadelphia
A world away from the coaly Tyne
Sailing to Philadelphia
To draw the line
The Mason-Dixon line”
“Sailing to Philadelphia”, Mark Knopfler

As I’ve said in the past, I consider it a travesty when a SaaS provider disallows direct access to the data in its native storage, such as by ODBC and OLE DB providers, and force you to use file extracts or APIs (often horrible and typically designed for app integration and not DW loads). This greatly inhibits data integration scenarios, such as extracting data for data warehousing. I wrote on this subject many times, including here, here and here.

Continuing on this subject, let’s consider two other vendors: Paylocity and Element.

SaaS Loser: Paylocity

Like Workday, Paylocity is a popular HR cloud platform. And like Workday, Paylocity doesn’t provide direct access to their database citing “security and IP concerns”. Instead, you must resort to “work in progress” APIs. Or opt for Paylocity pushing file extracts to an SFTP server set up by you or them.

In both cases, Paylocity charges a setup fee and per-employee fee. This will be the equivalent of putting your money in the bank and they charging you a withdrawal fee for every dollar you get out. This is actually not a far-fetched example considering that banks in some countries have started deposit fees. And who knows what lies ahead with the resurgence of socialism, but I digress…

SaaS Winner: Element

Element is a niche ERP system for environmental testing. Element stores data in an Azure SQL database. They provide direct access to the database as though it’s on-premises data store. Getting write access is not an issue if you are fine with the usual disclaimer. This proved extremely useful in a current project where complex business rules require creating temporary and permanent tables. Why can’t we have more of these SSAS vendors?

When it comes to choosing a SaaS vendor, you must draw a line: is it your data that must be easily accessible or is it vendor’s property with strings attached?

Atlanta Microsoft BI Group Meeting on November 3rd (Semantic Link Labs: A Link to the Future)

/0 Comments/in , /by

Atlanta BI fans, please join us in person for our next meeting on Monday, November 3rd at 18:30 ET. Jason Romans (Microsoft MVP) will show you how to use Semantic Link Labs to troubleshoot unreliable reports and semantic models. And your humble correspondent will walk you through some of the latest Power BI and Fabric enhancements. Improving will sponsor the meeting. For more details and sign up, visit our group page.

Delivery: In-person
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 (news, Power BI latest, sponsor marketing)
19:00-20:15 Main presentation
20:15-20:30 Q&A

Overview: It’s dangerous to go alone—take Semantic Link Labs!
When users are the first to discover that a Power BI report is broken, the damage is already done. Trust is lost, adoption slows, and credibility suffers. Instead of wandering into these traps unprepared, what if you had the Master Sword in hand—ready to defeat broken models and guard against treacherous usability pitfalls? That’s the power of Semantic Link Labs.

In this session, we’ll set out on a quest through Microsoft Fabric notebooks and pipelines, using Semantic Link Labs as our weapon and shield against unreliable reports. Along the way, we’ll face down the “mini-bosses” of BI development:
• Reports that collapse due to structural changes
• Models that underperform because best practices were skipped
• Usability pitfalls that make reports “technically fine” but functionally broken for end users

You’ll learn how to install and configure Semantic Link Labs, explore its legendary features, and see how it integrates seamlessly into Fabric. We’ll then take it a step further, automating health checks and governance with notebooks and pipelines—turning one-time fixes into repeatable spells.

By the end of this adventure, you’ll uncover your own “Triforce of Best Practices”—a report that tracks the best practices of all semantic models in your environment. You’ll leave equipped with a map, a shield, and the Master Sword itself: the tools you need to keep your BI world in legendary shape, where broken reports are discovered early, performance issues are vanquished, and best practices reign supreme.

Speaker: Jason Romans is a Business Intelligence engineer in Nashville, TN working with the Microsoft Business Intelligence stack. Jason is a Microsoft MVP who started his career as a DBA and over the years moved to working in his passion of Business Intelligence and data modeling. His first computer was a Commodore 64 and he’s been hooked ever since.
Blog: www.thedaxshepherd.com
Sessionize: https://sessionize.com/jason-romans/
LinkedIn: https://www.linkedin.com/in/jason-r-sql-jar

Sponsor: Improving is a leading IT professional services firm committed to helping companies achieve lasting success through modern technology. With core expertise in AI, Data, and Applications, we specialize in transforming legacy systems, building cloud-native platforms, and delivering intelligent, future-ready solutions for today’s complex business needs.

PowerBILogo

Replicating BigQuery to Fabric

/0 Comments/in /by

A recent engagement required replicating some DW tables from Google BigQuery to a Fabric Lakehouse. We considered the Fabric mirroring feature (back then in private preview, now publicly available) and learned some lessons along the way:

1. 400 Error during replication configuration – Caused by attempting to use a read-only GBQ dataset that is linked to another GBQ dataset but the link was broken.

2. Internal System Error – Again caused by GBQ linked datasets which are read-only. Fabric mirroring requires GBQ change history to be enabled on tables so that it can track changes and only mirror incremental changes after first initial load.

3 (Showstopper) The two permissions that raised security red flags are bigquery.datasets.create and bigquery.jobs.create. To grant those permissions, you must assign one of these BigQuery roles:

• BigQuery Admin

• BigQuery Data Editor

• BigQuery Data Owner

• BigQuery Studio Admin

• BigQuery User

All these roles grant other permissions and the client was cautious about data security. At the end, we end up using a nightly Fabric Copy Job to replicate the data.

In summary, the Fabric Google BigQuery built-in mirroring could be useful for real-time data replication. However, it relies on GBQ change history which requires certain permissions. Kudos to Microsoft for their excellent support during the private preview.

Atlanta Microsoft BI Group Meeting on October 9th (Everything You Want to Know About SQL Databases in Fabric)

/0 Comments/in , /by

Atlanta BI fans, please join us in person for our next meeting on Thursday, October 9th (note that we are meeting on Thursday for this meeting) at 18:30 ET. Sukhwant (Senior Product Manager, Microsoft) will explain why you should consider Fabric SQL databases. And your humble correspondent will walk you through some of the latest Power BI and Fabric enhancements. For more details and sign up, visit our group page.

Delivery: In-person
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 (news, Power BI latest, sponsor marketing)
19:00-20:15 Main presentation
20:15-20:30 Q&A

Overview: Microsoft Fabric is an all-in-one analytics platform, right? Wrong! With the introduction of SQL databases last year, we now have an all-in-one data platform. During this session you will hear directly from the product team about why we added SQL databases to Fabric, who should be using them, how this is different from Azure SQL databases, how to get started through an end-to-end demo, and the integration story with the rest of the platform.

If you’re a DBA that’s been trying to move applications for running SQL or a business user with limited database skills and no DBAs to be found, you’ll want to hear all about this exciting new offering that is simple, automated, and optimized for AI.

Speaker: Sukhwant has served as a Product Manager at Microsoft for the past few development cycles. During this time, she’s focused on the entire product management lifecycle, from working with development teams and user experience to collaborating with cross-functional teams to drive customer satisfaction in ensuring our products not only meet but exceed customer expectations.

Before joining Microsoft, she held various full-time/contracting roles as a technology leader for over two decades in software lifecycle development, system integration and enterprise architecture design. Her expertise extends to Data Strategy, Analytics, and Web Content Management. Throughout her career, she has successfully led numerous projects, both small and large, from inception through to implementation. She is a proponent of the servant-leader philosophy, which aims to continuously improve and empower those she works with.

Sponsor: At CloudStaff.ai, we’re making work MORE. HUMAN. We believe in the power of technology to enhance human potential, not replace it. Our innovative AI and automation solutions are designed to make work easier, more efficient, and more meaningful. We help businesses of all sizes streamline their operations, boost productivity, and solve real-world challenges. Our approach combines cutting-edge technology with a deep understanding of human needs, creating solutions that work the way people do! https://cloudstaff.ai

PowerBILogo

Atlanta Microsoft BI Group Meeting on September 8th (End-to-End Azure DevOps for Data Engineering in Microsoft Fabric)

/0 Comments/in , /by

Atlanta BI fans, please join us in person for our next meeting on Monday, September 8th at 18:30 ET. Jeff Levy (Data Architect @ Protiviti) will show us how to implement Azure DevOps for data engineering projects in Microsoft Fabric. And your humble correspondent will walk you through some of the latest Power BI and Fabric enhancements. For more details and sign up, visit our group page.

Delivery: In-person
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 (news, Power BI latest, sponsor marketing)
19:00-20:15 Main presentation
20:15-20:30 Q&A

Overview: This session explores how to implement Azure DevOps for data engineering projects in Microsoft Fabric. You’ll learn the following:

  • Version Control Lakehouse assets (Pipelines / Notebooks / SQL Objects)
  • Manage environments with reusable YAML templates
  • Apply CI/CD Practices via the DevOps Build and Release Pipelines

The session is ideal for data engineers and DevOps practitioners aiming to bring agility, consistency, and governance to Fabric-based solutions.

Speaker: With over 12 years of expertise in designing, implementing, and optimizing data warehouse solutions, Jeff Levy (Data Architect @ Protiviti) is a seasoned Data Warehouse Architect specializing in SQL Server and Azure ecosystems. He has a proven track record of transforming complex data requirements into scalable, high-performance architectures that empower data-driven decision-making. These solutions have leveraged the full capabilities of Azure technologies, such as Azure Synapse Analytics, Databricks, and Microsoft Fabric. With a deep understanding of SQL, data modeling, and ETL processes, he has delivered many scalable and economic solutions to fit client needs. Jeff has worked across many verticals including Healthcare, Telecom and Retail / Consumer Product Goods (CPG)

Sponsor: Protiviti

PowerBILogo

Migrating Fabric Import Semantic Models to Direct Lake (Part 2)

/0 Comments/in /by

I’ve previously shared my experience with migrating a Fabric imported semantic model to Direct Lake. This blog follows up with additional observations about performance. The following screenshot is taken from the Fabric Capacity Metrics app and it shows the maximum metrics over 14 days. The two enclosed items of interest are the original imported semantic model (the first item on the list) and its DL counterpart (the seventh item on the list).

A screenshot of a computer AI-generated content may be incorrect.

Memory utilization

As I explained in the first part, the whole reason for taking this epic journey was to solve the out-ot-memory blowouts and constant pressure to climb the Fabric capacity ladder. With 1/5 of the user audience testing the dataset in production environment, that dataset grew to a maximum of 25 GB memory utilization which is in line with the imported model. It could have been interesting to downgrade the capacity, such as to F64, and observe how the DL model would react to memory pressure. However, as shown in the screenshot, the client had other large semantic models that can exhaust the F64 25 memory grant so we couldn’t perform this test.

Again, what we are saving here is the additional memory required for refreshing the model. In a sense, we shifted the model refresh to replicating the data from Google Big Query to a Fabric lakehouse. On the downside, an error during the replication process could leave the replicated tables in an inconsistent state (and user complaints because reports would show no data or stale data) whereas a failure during refreshing the model would fall back on the old model (Fabric builds a new in-memory cache during model refreshing).

The team is currently exploring options to mitigate failures during replications, including incremental replication or using the Delta time-travel features. Replication errors aside, eliminating model refresh is a huge win.

CPU utilization

A while back, I got some feedback that an organization that attempted to switch to Direct Lake found that the capacity CPU utilization increased significantly causing them to revert to import mode.

I didn’t witness CPU pressure during production testing. Further, the team didn’t notice any report performance degradation or increased CU capacity utilization. If I must guess that organization didn’t force the model to Direct Lake Only, causing the model to go back between Direct Lake and Direct Query under certain conditions.

Summary

Assuming you have exhausted traditional methods to alleviate memory pressure, such eliminating high-cardinality column, incremental refresh, etc., Direct Lake is a viable option to conserve memory of Fabric semantic models. Unfortunately, it may require replicating your data to a Fabric lakehouse or migrating your data warehouse to Fabric so that it uses Fabric storage (Delta Parquet format) required for Direct Lake. If this is a new project and you expect large semantic models, your architecture should consider Fabric Data Warehouse or Lakehouse to take advantage of Direct Lake storage for your semantic models.

From Prompt to Insight: My Daily Dance with AI

/0 Comments/in /by

“I go checking out the reports, digging up the dirt
You get to meet all sorts in this line of work
And when I find the reason, I still can’t get used to it
And what have you got at the end of the day?
What have you got to take away?”

Private investigations, Dire Straits

Here we go

Me: “Write code to do this and that.”

LLM: “I’m glad to help. Here is the code.”

Me: “It doesn’t work because of this error <nasty error message follows>”

LLM: “You get this error because…Here is the correct code.”

Me: “Doesn’t work again because of this new error <nastier error message follows>.”

LLM: “You get this error because…Here is the correction of the corrected code.”

After N iterations and mutual blame, we either get eventually to working code or give up and start cursing each other. LLM usually quits first, claiming I have exhausted my quota, so I start harassing the next vendor.

Me: “Why didn’t get it right the first time?”

LLM: “That’s rude…I’m learning and I can make mistakes…don’t hurt my feelings.”

Teo’s top 5 LLM professional wishes

These typical exchanges inspired by top 5 LLM wishes:

  1. If you are still learning, why can’t you be more humble and less assertive? This reminds me of some members of my family whose level of assertiveness is a reverse correlation with their knowledge of the subject. But it could be that LLMs are designed to act as humans in this regard too.
  2. When it comes to code generation, can we use the latest versions, class signatures, etc.? We all know how quickly programing interfaces evolve.
  3. Even better, can you compile the code to ensure that at least I don’t get compile errors?
  4. Best, can you actually run the code instead of claiming that the code will produce the desired outcome?
  5. When you substantiate your claims with references, can you ensure that they do what I asked you to do? Can you display a warning that you’re reasoning over some code example that is N years old?

Admiration lives on

Other than that, I keep on being impressed with LLMs. Specifically, I’m impressed by their reasoning and code generation capabilities, especially when it comes to pioneering languages that have decided to plant their flag in lands unknown, such as Power BI DAX, Power Query M, and Azure Data Factory (whatever bizarre expression language it adopted).

As of now, I believe that experts and architects who have solid foundation skills are in position to gain the most as I won’t trust AI to make architectural or design decisions.

Speaking of being impressed, the latest gem I’ve discovered was Microsoft Copilot Screen Sharing. I used it recently to analyze charts from the Fabric Capacity Metrics app whose primary design goal appears to be leaving the user utterly confused or convinced that it’s time to upgrade their Fabric capacity (see these red spikes? time for upgrade!). In my humble opinion, its output could have been much more useful if it had a chart showing the average resource utilization instead of actual, but I digress. However, the Screen Sharing feature saved taking screenshots and intelligently pointed out what the issue was.

A screenshot of a computer AI-generated content may be incorrect.

On the downside, ChatGPT did a better job with screenshots. For example, it correctly identified ‘AS’ as Analysis Services workload and came up with better conclusions. Luckily, having multiple assistants it’s not an issue and they don’t complain unless you start abusing them…

 

Atlanta Microsoft BI Group Meeting on August 4th (Power BI Built-in Gems: Time-Saving Features You Should Be Using)

/0 Comments/in , /by

Atlanta BI fans, please join us in person for our next meeting on Monday, August 4th at 18:30 ET, which marks the 15th anniversary of the Atlanta Microsoft BI Group! Lakshmi Ponnurasan (a Microsoft Data Platform MVP and a Certified Power BI specialist) will show us how to apply Power BI time-saving built-in features to create stunning and impactful reports in less time. And your humble correspondent will walk you through some of the latest Power BI and Fabric enhancements. For more details and sign up, visit our group page.

Delivery: In-person
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 (news, Power BI latest, sponsor marketing)
19:00-20:15 Main presentation
20:15-20:30 Q&A

Overview: Power BI is like a treasure chest packed with powerful built-in features- except these gems are often hidden in plain sight, waiting to be discovered. These time-saving built-in features can help you create stunning, impactful reports in less time. In this session, you’ll witness these hacks in action, and by the end, you’ll have at least one new hack up your sleeve to impress your colleagues.

Who Should Attend? This session is perfect for anyone who knows the basics of Power BI or wants to discover its full potential. Just bring in your curiosity and a desire to learn! Session Takeaways:
1. Discover built-in Power BI hacks that you might have missed.
2. Watch real-time demonstrations of how these features can save you time.
3. Learn how to use these features to enhance your reports and increase your overall efficiency.

Walk Away With:
Exercise files, so you can go home, flex those new skills, and start wowing everyone with your Power BI skills!

Speaker: Meet Santhanalakshmi- a Microsoft Data Platform MVP and a Certified Power BI specialist and one of the four finalists of the 2025 Microsoft Power BI DataViz World Championships! Known for turning raw data into eye-catching, actionable insights, she blends creativity with deep technical know-how to build reports that truly stand out. By day, she’s a dynamic Product Lead juggling market research, testing, team collaboration, marketing, and content creation. By passion, she’s a speaker, blogger, and mentor- always eager to share tips, best practices, and encouragement with the data community, especially uplifting women in tech. Outside the data world, she’s a proud Corgi mom, outdoor enthusiast, and foodie on a mission to try it all!.

Sponsor: Teo Lachev (Prologika)

PowerBILogo