Power BI

The next figure shows how Semantic Link creates a bridge between data science and Business Intelligence by providing the possibility to leverage semantic model data in data science using a familiar set of tools: Notebooks, Python, and Spark, to name a few of the most popular.

This library extends the generic sempy library and consists of multiple ready-made functions you can use out-of-the-box to leverage Semantic Link feature in Microsoft Fabric

Writing Python code is not something you would expect from every data professional. Hence, the credit for the ease of use of various Semantic Link implementations

The following figure illustrates a dependency tree of the FactInternetSales table from the sample Adventure Works database. I was able to plot the dependency tree by executing literally six lines of Python code.

Despite all of the other data science and machine learning tools and features, such as MLFlow, SynapseML, and AutoML, I sincerely consider Semantic Link

s. I firmly believe that what was a missing link before, now with Semantic Link becomes the strongest link, as this feature finally enables bridging the gap between data science and Business Intelligence.

If you take a look at the official documentation, you’ll learn that “Semantic link is a feature that allows you to establish a connection between semantic models and Synapse Data Science in Microsoft Fabric”. Doesn’t sound too exciting, right?

2. To walk through 3 Copilot senarios: generating code, answering data questions, and generating reports.

Copilot is a generative AI tool, and generative AI is a form of “narrow” or “weak” artificial intelligence. A simple explanation is that tools like Copilot provide a chatbot interface to interact with pre-trained foundation models like GPT-4, which might have additional tuning on-top. Inputs to these large-language models (LLMs) might use additional pre-processing with contextual data (which is called grounding) or post-processing by applying filtering or constraints (i.e. for responsible AI, or AI safety). These foundational models are trained on large amounts of data to learn patterns and can then generate new data that should resemble the inputs while also being coherent and context appropriate.

They work in different ways:  The different Copilots might have subtly different architectures, use different foundational models (like Chat-GPT4 or DALL-E3), and return different results.

To paraphrase what Copilot does in steps, it:

Setting the row label and key column properties for tables in your semantic model. These properties help determine how to aggregate data when there are duplicate values identified by another column. The example from Chris Webb’s blog is avoiding grouping customers only by name, since some customers have the same name, but are identified uniquely by their customer ID.

To get this output, Copilot will take your prompt and grounding data to use with the LLM. This grounding data is perhaps a misnomer, because it’s not necessarily the data from your data items. Rather, it refers to the metadata that defines i.e. your semantic model and how you’ve set it up to work. For instance, Copilot looks at the names of the tables, columns, and measures in your model, as well as their properties, including the linguistic schema (i.e. synonyms), among others.

Generative AI is good at use-cases with soft accuracy. Unfortunately, in business intelligence, almost nothing fits in that box. When it comes to a BI solution, the business expects only one answer – the correct one. An incorrect or even a misleading output can lead to wrong business decisions and disastrous results.

They aren’t generally deterministic: When you submit a prompt multiple times to Copilot or Chat-GPT, you aren’t guaranteed to get the same answer back. However, in other tools that allow more tuning of LLM responses, you can set the temperature property lower (or colder) which makes results more deterministic.

Remember that Copilot is not deterministic. If we run this exact same prompt again in another query, we may get a completely different result with different issues. This might depend on other grounding data that Copilot is taking from that different session, i.e. if you submitted other prompts before.

additionally, Copilot is introducing new problems because of a flawed prompt; it’s computing the difference between Order Date and the Date from the Date table, which has a relationship… to Order Date. So even if we correct the filter to “Express Order”, the result will be incorrect; it will be 0.

Put another way, the more you know about a topic, the better you can tell what’s right and what’s wrong. When you know more, you’re also more aware of what you don’t know, what’s possible, and what makes sense. When using generative AI, this is extremely helpful, because you can have a more productive sparring session and less frequently fall victim to their errors or hallucinations.

What this means is that novices and beginners are more susceptible to wasting time and resources using AI. That’s because they are more likely to encounter “unknown-unknowns” that are harder to understand and apply, but they’ll also struggle more to identify bullshit like errors, hallucinations, or logical flaws.

In this scenario, the user asks Copilot for the MTD Sales for August 2021. This initially returns an error; MTD Sales is a synonym for Turnover MTD, so the user repeats the question using the actual measure name in the model. Copilot proceeds to return the Turnover MTD, but for August 2022 instead of August 2021. The current report page is showing MTD Sales, where the month is August (from a slicer selection) and the year is 2022 (from the filter pane). For its answer, Copilot refers to a matrix visual that shows this number.

EXAMPLE 1: USING COPILOT TO HELP WRITE DAX CODE

EXAMPLE 2: USING COPILOT TO ANSWER DATA QUESTIONS

When submitting another prompt after changing the report page, Copilot then creates a card stating that Turnover MTD is 5bn for August 2021, which is not only incorrect, but I have no idea how it came up with this number, because when clicking “Show reasoning”, Copilot simply re-phrases the prompt.

In the previous example where we generated code, the user could get a better result. Here, the prompt already seems quite specific and descriptive for such a simple question. However, even if the user gets extremely specific, referencing the exact column names and values, Copilot still returns an incorrect result.

The user slightly adjusts the prompt, removing possible ambiguities from the column names, which include examples. But again, the result is wrong; Copilot incorrectly returns the turnover for August 2022 instead of 2021.

Obviously at this point any normal user would give up. The damage has been done. But for the sake of argument, let’s press forward. The problem might not be the prompt. Could it be the model? The data?

Additionally, Copilot seems to be referencing the report page and grabbing a result from a visual, when I actually want it to query the model.

EXAMPLE 3: GENERATING REPORTS WITH COPILOT

At this point, however, I realized that I actually intended for Copilot to use the on-time delivery percentage and not the values. I should have specified this explicitly in the prompt, but at this point, I wonder “Do I generate a new report page, or replace these visuals myself?” But I wasn’t interested in using more of my available capacity.

IF THE REPORTS LOOK NICE, IT’S BECAUSE OF A HUMAN AND NOT COPILOT The example reports from Copilot demonstrations look nice at first glance, especially before scrutiny. However, it’s important to emphasize that the initial aesthetic appeal of these reports is not an AI output, but due to some simple design controls put in by a human.

View fullsize

Copilot reports look nice because a human followed some good design practices when setting the default properties for Copilot reports. Click the image to enlarge it in a lightbox.

A generated report page by Copilot. The prompt was “Title: On-Time Delivery by Key Account Description: An overview of the total On-Time Delivery percentage in lines, value, and quantity, the trend, and a breakdown by Key account for these three metrics”. Click the image to enlarge it in a lightbox.

The result when asking Copilot to create a matrix of OTD by Key Account. Copilot adds the conditional formatting without asking. Click the image to enlarge it in a lightbox.

COPILOT PROBABLY ISN’T A DECIDING FACTOR FOR YOU TO GET AN F64 Copilot in Fabric and Copilot for Power BI are evolving over time. Undoubtedly, eventually, it will grow to encompass much more functionality and scope, and it will improve. However, in its current state, I don’t really consider it to be a deciding factor for purchasing Fabric or an F64 SKU. Of course, this might change, and I’ll re-evaluate this as its capabilities evolve.

One final remark is not about Copilot specifically, but generative AI technology, as a whole.

Group changes into distinct releases with version history.

Depending on how you author content, you'll make different decisions about how to manage it. For instance, for Power BI reports and semantic models, a Power BI Desktop (.pbix) file has fewer options for version control compared to the Power BI Desktop project (.pbip) format. That's because a .pbix file is a binary format, whereas the .pbip format contains text-based human-readable content and metadata. Having human-readable content and metadata allows for easier tracking of model and report changes by using source control. Source control is when you view and manage changes within content to its code and metadata

Excel: A client tool for pivot tables and live connected tables that work with a semantic model. Power BI Report Builder: A desktop application for creating paginated report (.rdl) files.

You can develop and test content without affecting the content that's currently in use. This avoids changes that can cause unintentional disruption to content in production. You can use separate resources for developing and testing content, like using separate data gateways or Fabric capacities. This avoids that resources used for development and testing disrupts production workloads, causing slow data refreshes or reports. You can create a more structured process to develop, test, and release content by having discrete environments for each of these stages. This helps you to improve productivity.

Test and production workspaces

Private workspace with Git integration When delivering business-critical content, each developer can also use their own, private workspace for development. In this scenario, a private workspace allows content creators to test content in the Fabric portal, or use features like scheduled refresh without risking disruption to others in the development team. Content creators can also develop content in the Fabric portal here, such as dataflows. Private workspaces can be a good choice when you are managing content changes by using Git integration together with Azure DevOps.

Alerts: You should set up alerts for when others add, remove, or modify critical files. Scope: Clearly define the scope of the remote storage location. Ideally, the scope of the remote storage location is identical to the scope of the downstream workspaces and apps that you use to deliver content to consumers. Access: You should set up access to the remote storage location by using a similar permissions model as you have set up for your deployment pipeline permissions and workspace roles. Content creators need access to the remote storage location. Documentation: Add text files to the remote storage location to describe the remote storage location and its purpose, ownership, access, and defined processes.

Fabric Git integration has some limitations with the supported items and scenarios. Ensure that you first validate whether Fabric Git integration best suits your specific scenario, or whether you should take a different approach to implement source control.

Use branches Content creators achieve collaboration by using a branching strategy. A branching strategy allows individual content creators to work in isolation in their local repository. When ready, they combine their changes as a single solution in the remote repository. Content creators should scope their work to branches by linking them to work items for specific developments, improvements, or bug fixes. Each content creator creates their own branch of the remote repository for their scope of work. Work done on their local solution is committed and pushed to a version of the branch in the remote repository with a descriptive commit message. A commit message describes the changes made in that commit.

You typically start the content lifecycle by performing BI solution planning. You gather requirements to understand and define the problem that your solution should address, and arrive at a solution design. During this planning and design stage, you make key decisions to prepare for the later stages.

Which item types do you expect to create, and how many of each? For instance, will you create data items like dataflows or semantic models, reporting items like reports or dashboards, or a combination of both? How is the content delivered to content consumers? For instance, will consumers use data items to build their own content, will they only view centralized reports, or a combination of both? How complex is the content? For instance, is it a small prototype, or a large semantic model that encompasses multiple business processes? Do you expect the scale, scope, and complexity of the content to grow over time? For instance, will the content encompass other regions or business areas in the future? How long do you expect the business to need this content? For instance, will this content support a key initiative of the business that has a finite timeline?

Using Semantic-Link-Labs to build the solution The good news is that Michael Kovalsky from elegantbi.com has created the Semantic-Link-Labs which allows this to be achieved using a Notebook. I first installed the Semantic-Link-Labs