Data Meerkat

Fabric Notification Bot for Microsoft Teams

Thu, 17 Apr 2025 11:00:00 +0200

Power BI provided a wide range of notification options, each with its own advantages and disadvantages, often requiring us to combine them to achieve the desired result. Historically, we primarily relied on Subscriptions, Alerts, Outages Emails, and Refresh Error Emails. Combining these options was complex and inefficient. With the introduction of the ExecuteQuery endpoint in the Power BI REST API, entirely new possibilities opened up. By sending a DAX query to a selected dataset and processing the returned data, we can create virtually any custom notification, enabling data-driven notifications. This endpoint also came with built-in support for Microsoft Power Automate and Logic Apps, significantly simplifying the process due to their native connectors to other services like Exchange, SharePoint, and Microsoft Teams (including support for Adaptive Cards).

Then came Microsoft Fabric, introducing additional capabilities across its workloads. In Real-Time Intelligence, we gained the Activator, which can trigger a notification or Power Automate flow when a milestone is reached in our data. In Data Factory, the Data Pipeline gained native integration with Office 365 Outlook and Microsoft Teams. Unfortunately, both options have limitations for more complex notifications. The Activator is restricted to specific points in time for measured milestones, while the Data Pipeline is limited to outputs of individual activities and lacks support for more advanced features like Adaptive Cards, which allow for more structured, complex, and interactive notifications.

At first glance, the most versatile option might seem to be the existing support within Power Automate and Logic Apps. However, these also have their drawbacks. For me, the most significant issues are:

Triggering flows from Microsoft Fabric requires using an HTTP Trigger, which is not very secure and needs to be protected with an API Key. Parameters must then be passed in the HTTP Query to the flow.
Complex input parameters, such as JSON objects, cannot be passed from Microsoft Fabric, limiting the ability to handle more intricate data structures.
Adaptive Cards are sent via the Power Automate or Workflows Teams App. If this app is used by multiple people or flows, the notification stream can become overwhelmed, making it easy for users to overlook a specific message or card that might be critical.

Reflection on the Problem

Let’s consider a report dealing with service requests. The report presents the current state, showing the status of individual tickets—assigned, unassigned, resolved, and SLA compliance. The report is intended for a manager to monitor whether everything is running smoothly or if intervention is needed to redistribute tasks or adjust priorities. The manager, however, has other responsibilities requiring their attention. While the team generally performs well, reducing the need for frequent intervention, the manager must still check the report regularly to catch potential issues early. Over time, as problems become less frequent, the manager might check the report less often, potentially missing critical issues when they arise. A tool that can proactively address such “simple” problems and provide a sense of security without requiring the manager to leave their primary tools would be beneficial.

One option could be to create a dashboard with an alert card that triggers an activity in Power Automate, notifying the manager via a message or adaptive card in chat. Similarly, this could be achieved using a DAX query on the semantic model with a similar output. However, as mentioned earlier, users are contacted through the native Workflow app in MS Teams, where notifications from various unrelated flows can accumulate, potentially leading to important messages being overlooked. Using Outlook might be even more problematic, as critical emails can easily get lost in the flood of messages.

Another example could be a report on accounting errors within a company, where multiple accountants process incoming and outgoing transactions. Since accounting reflects the company’s financial health, everything must be accurate. However, amidst numerous transactions, errors can occur. Requiring accountants to check a report for errors after every semantic model update reduces their productivity. Instead, a card summarizing any errors (if they exist) could be sent directly to their chat, bringing the information closer to those who need it.

Microsoft Fabric is not just about reports; it’s a comprehensive data platform. What if a data load fails—not entirely, but partially (e.g., one table fails to load), or worse, a table appears to have loaded but contains outdated data? If Load_Tracking tables are implemented, such issues can be detected relatively easily. However, this information must be extracted and communicated. Power Automate (Workflow) has a connector for SQL Server, allowing it to connect to Fabric Lakehouse T-SQL Analytical Endpoint or Fabric Warehouse T-SQL Endpoint. However, processing the retrieved data within Power Automate is not its strong suit. Additionally, the flow would need to run regularly to catch such issues, requiring a mechanism to track whether the problem has already been reported.

I could add more scenarios, as this is a broader issue than it might initially seem. It’s essential to seek a generic solution that delivers relevant information in a clear format to tools that users regularly use. The information should arrive as soon as possible after an issue, error, or suspicion arises. The communication channel should be targeted to avoid mixed signals or overlooked messages due to noise.

Direction

There are undoubtedly multiple options, but I decided to explore one specific path. I knew I wanted to use Adaptive Cards in Microsoft Teams because they allow me to send structured components with icons, images, separators, and buttons for easy navigation and redirection to specific locations, such as a report for more details.

Cards can be sent to Teams channels or chats. Sending them to channels is not my goal (although it’s simpler since Webhooks can be used without additional complexity) because the issues are usually intended for specific individuals or groups. Sending messages directly to chats is a more direct option, reducing the risk of messages being ignored amidst numerous active channels awaiting acknowledgment. However, sending messages to chats requires an existing chat. For a chat to exist, there must be at least two participants (User A, User B, … User Z). The only exception is a chat with oneself, which I will ignore for now.

Another challenge is obtaining the permissions to send messages and attachments, as Adaptive Cards are technically message attachments, and Microsoft Teams handles their display. This requires specific API Permissions for such operations. Additionally, a mediator is needed to send these messages. Using a user to send the content requires access to their user token with the appropriate scope permissions. Since the goal is to create a tool that operates independently once deployed, relying on user authentication for token refreshes is not ideal. Another option is a Teams App, which requires a Service Principal and an Azure Bot (or Bot Framework).

Who or what will provide the Adaptive Cards to the bot’s identity and send them to the appropriate chat? This is where the choice becomes diverse. Options include Azure Web App, Azure Functions, etc. For this demonstration, I’ll describe the Azure Web App approach. Why? Because I spent a lot of time on it and learned a lot. Azure Functions might be simpler for this scenario (especially since they can be cost-free and operate reactively, waking up only when called after a long period of inactivity, whereas Azure Web App always has a consistent response time). However, they are not as engaging. With a Web App, you must deploy your system to handle incoming requests, route them to appropriate endpoints, and process them. This allows you to implement custom security measures, and since it’s within Azure, you can configure specific routing to prevent unauthorized access to your Web App.

Finally, a logical unit, such as a Python Notebook, is needed to execute operations like SELECT queries, DAX query execution, etc., based on strict instructions or metadata. The notebook will convert the results into an adaptive card, which it sends with the chat ID to the Web App endpoint. The Web App then forwards the content to the bot’s identity for delivery to the chat.

Simple, right? No? I didn’t think so either, but it’s doable, and once you go through it, it becomes relatively straightforward.

Here’s what’s needed:

Azure Web App / Azure Functions / …
Azure Bot
Service Principal (including API Permissions)
Microsoft Teams App
Microsoft Teams Chat ID
Python Notebook

Let’s start from the beginning and proceed step by step to ensure everything fits together.

Creating the Owl Bot

Why an owl? Because you hoot content at it, and it hoots it back to the user. It ignores (almost) everything else.

Creating an Azure Bot

This is the simplest part. You need to create an Azure Bot resource in Azure, which will serve as the entity for registration within the Microsoft Teams App and as the identity used by the Service Principal to send content to chats.

In the Azure Bot Service, create a Bot Service of type Azure Bot. Azure Bot

When creating the bot, you can choose a pricing tier. The primary difference is the guaranteed SLA for premium messages. If you don’t need this and are fine with messages being delivered as soon as the service is available, choose the Free tier. Pricing Tier

For the application type, select the one that suits your needs. For my scenario, I don’t need a Multi Tenant application; a Single Tenant application is sufficient since my bot will operate only within my tenant. If you already have an existing Service Principal you want to associate with the bot, you can do so in the final setup step on the Basics blade during creation. Alternatively, you can create a new Service Principal here.

If you chose Single Tenant, you can confirm in the Configuration tab of the created bot that the App Tenant ID matches your Tenant ID. In the Channels tab, enable Microsoft Teams channel support. After confirming the initial prompt, simply click Apply unless you want to change the bot’s support to Microsoft Teams Government. Teams Channel Support

We’ll return to this bot later, but for now, this is all we need.

Creating an Azure Web App

Within Azure App Service, you can create a Web App using the UI. Alternatively, you can use the Azure CLI, which needs to be installed first but allows for much faster and more efficient resource creation and deployment.

Here is an example template for creating an Azure Web App, including an App Service Plan:

az login

az appservice plan create --name {name-of-app-plan-service} --resource-group {resource-group-name} --sku B1 --is-linux

az webapp create --name {web-app-name} --resource-group {resource-group-name} --plan {name-of-app-plan-service} --runtime \"NODE|20-lts\"

In this template, I am creating an App Service Plan with SKU B1, which designates a Basic plan. However, for simpler purposes, you can use the lower SKU F1, which is free.

App Service Plans Options

Before we start working on the code for the Web App, it’s a good idea to set up the environment variables that the code will use. In the Settings section under Environment variables, you can either add variables one by one or use the Advanced edit option to add them all at once:

[
    {
        "name": "MICROSOFT_APP_ID",
        "value": "",
        "slotSetting": true
    },
    {
        "name": "MICROSOFT_APP_PASSWORD",
        "value": "",
        "slotSetting": true
    },
    {
        "name": "MICROSOFT_APP_TENANT_ID",
        "value": "",
        "slotSetting": true
    },
    {
        "name": "MICROSOFT_APP_TYPE",
        "value": "SingleTenant",
        "slotSetting": true
    }
]

Fill in the respective values from the Service Principal created along with the Azure Bot or the one you added to the Azure Bot instance during its creation. The Microsoft APP Password corresponds to the Service Principal Secret. Don’t forget to click Apply; otherwise, the variables won’t be saved. If you want to create a Multi Tenant bot, change the APP_TYPE accordingly.

A strong warning: If you decide to create your own bots, focus on their security—both in terms of code and configuration—to ensure they can only be used by your organization. Also, ensure that the bot only listens to commands from authorized individuals or services. In this example, I am not demonstrating security but focusing solely on deploying a simple bot to fulfill the given scenario.

The Brain of the Web App Bot

For the chatbot to perform any actions, it needs a **"brain"** to process incoming messages and execute actions. Within Azure Web App, you can use various languages and frameworks. After several attempts to solve this problem using Python, I decided to use **TypeScript** and **Node.js**. You may have noticed this choice during the Web App creation step, where I selected the runtime **NODE|20-lts**. The project structure is as follows:

.
├── src
│   ├── app.ts
│   ├── controllers
│   │   ├── adaptiveCardController.ts
│   │   └── testController.ts
│   └── routes
│       ├── adaptiveCardRoutes.ts
│       └── testRoutes.ts
├── .env
├── package-lock.json
├── package.json
└── tsconfig.json

Whole project is available here: GitHub - Azure Web App Bot Template.

The ‘src’ folder contains all the code needed to run the bot. The ‘controllers’ folder contains individual controllers that process specific endpoints. The ‘routes’ folder contains the routes that are called and passed to the appropriate controllers. The ‘app.ts’ file in the ‘src’ folder is the main file that starts the server and sets up the routes. At the same level as the ‘src’ folder, there is an ‘.env’ file containing the environment variables needed to run and test the bot locally. The ‘package.json’ and ‘package-lock.json’ files list all the required packages, and the ‘tsconfig.json’ file contains TypeScript settings.

To test if everything works, ensure you have Node.js and npm installed. Then, run the following commands in the project folder:

npm install
npm build
npm start

If everything runs correctly, you should see in the console that the server is running on port 3000. If you have Postman or another API testing tool, you can test the test endpoint:

curl -X GET http://localhost:3000/api/test

This should return a 200 OK response with the message “Work!”. This confirms that the server is running and ready to handle incoming requests. If you’ve also filled in the ‘.env’ file, the bot will attempt to connect to the Service Principal and authenticate when calling the ‘/api/adaptive-card’ endpoint. However, this endpoint requires an Adaptive Card JSON and a Chat ID. While we can already provide the first part, we don’t yet have the Chat ID because the bot hasn’t been linked to the Web App or Microsoft Teams, nor has the Service Principal been granted the necessary API permissions. So, for now, let’s ignore this endpoint and focus on deploying the code to Azure Web App.

The Visual Studio Code extension called Azure App Service can help deploy the code to Azure Web App. This extension allows you to deploy your codebase directly from the VS Code interface to Azure Web App. It’s very simple and quick. Just connect to your Azure account, select the Resource Group and Web App where you want to deploy your code, and the rest happens automatically.

Deploy Azure Web App

Deployment to Azure Web App may take some time because your code will be passed to Oryx, which builds the environment and deploys the code. Before sending API requests to the newly deployed endpoint, check that Oryx has completed its work. You can verify this in the Azure Portal under the Deployment > Deployment Center section of your Web App, where you can view logs. If the last log entry shows “Success (Active)”, everything is ready, and you can start testing. Otherwise, wait for Oryx to finish or make the necessary adjustments based on the logs.

Now let’s link the Web App with the Bot. For this, you’ll need the Default domain of the Azure Web App, which is the URL where the Web App is deployed. You can find this in the Overview section of your Web App. It looks like this:

https://{web-app-name}.azurewebsites.net

Enter this address into the Azure Bot’s Configuration tab in the Messaging endpoint field, appending ‘/api/messages’ to the end.

Azure Bot Messaging endpoint configuration

API Permissions for Bot Service Principal

To enable the bot to send messages to a chat, you need to configure API Permissions for the Service Principal created along with the Azure Bot. In the Azure Portal, navigate to Azure Active Directory > App registrations and locate your bot. In the API permissions tab, add the following permission:

GraphAPI > Application Permissions > Teamwork.Migrate.All

Creating a Microsoft Teams App

With the Web App and Azure Bot ready, it’s time to create a Microsoft Teams App, which allows us to connect our bot to Microsoft Teams. The Microsoft Teams App requires a manifest file containing information about what the app does and its permissions. This manifest is then uploaded to Microsoft Teams, making the app available to users.

Here’s an example of the manifest:

{
    "$schema": "https://developer.microsoft.com/en-us/json-schemas/teams/v1.16/MicrosoftTeams.schema.json",
    "manifestVersion": "1.16",
    "version": "1.0.0",
    "id": "{app_id}",
    "packageName": "com.microsoft.teams.extension",
    "developer": {
        "name": "{developer_name}",
        "websiteUrl": "{site_url}",
        "privacyUrl": "{site_url}",
        "termsOfUseUrl": "{site_url}"
    },
    "icons": {
        "color": "{icon_192.png}",
        "outline": "{onboarding_32.png}"
    },
    "name": {
        "short": "Fabric notifications",
        "full": "Fabric notifications"
    },
    "description": {
        "short": "{short_description}",
        "full": "{full_description}"
    },
    "accentColor": "#FFFFFF",
    "bots": [
        {
            "botId": "{bot_id}",
            "scopes": [
                "personal"
            ],
            "isNotificationOnly": true
        }
    ],
    "composeExtensions": [],
    "configurableTabs": [],
    "staticTabs": [],
    "permissions": [
        "identity",
        "messageTeamMembers"
    ],
    "validDomains": [
        "*.powerapps.com"
    ]
}

The manifest contains details about the app, such as its name, description, icons, and permissions. If the app is intended solely as a notifier (as in this case), set ‘isNotificationOnly’ to ‘true’. Replace all placeholder values in ‘{}’ with actual values. Note that the ‘id’ is not the Azure Bot or Service Principal ID but the Microsoft Teams App ID, which is generated when creating the app. If deploying via the manifest, you’ll need to generate the Teams App ID, which can be done using tools like the Visual Studio Code extension TMDL UUID Generator. The ‘bot_id’ should be the Service Principal ID associated with the Azure Bot, the same one used in the Web App. Include 192x192 and 32x32 images alongside the manifest in a ZIP file.

Upload the ZIP file via the Admin - Microsoft Teams - Manage Apps page using Actions (top-right corner) > Upload new App. If successful, you’ll see a confirmation message: Uploaded new App

The app will then be registered in the internal Teams Apps Store and should appear in Microsoft Teams for users to install after some time. If it doesn’t, check permissions and ensure the app is allowed for the tenant. If you don’t want to wait for the app to propagate to the Teams Store, you can install it for a user using the REST API:

curl --location 'https://graph.microsoft.com/v1.0/users/{user-id | user-principal-name}/teamwork/installedApps' \\
--header 'Content-Type: application/json' \\
--header 'Authorization: Bearer xxxx' \\
--data-raw '{
   "teamsApp@odata.bind" : "https://graph.microsoft.com/v1.0/appCatalogs/teamsApps/{teams-app-id}"
}'

The Bearer token can be obtained using a Service Principal with permissions for the Teams App Catalog. Ensure the following Microsoft Graph API permissions are configured:

Delegated: TeamsAppInstallation.ReadWriteSelfForUser, TeamsAppInstallation.ReadForUser
Application: TeamsAppInstallation.ReadWriteSelfForUser.All, TeamsAppInstallation.ReadForUser.All

If successful, you’ll receive a ‘201 Created’ response, and the app should appear in Microsoft Teams for the specified user after clearing the cache. However, this doesn’t complete the process, as you’ll also need the Chat ID to send messages.

Obtaining the Chat ID

The Chat ID is not straightforward to retrieve. While you have the Teams App ID, it’s not directly tied to the user. Once you have the user’s app installation ID, you can retrieve the Chat ID using the following query (note that it may take a few minutes for the installation to propagate):

curl --location 'https://graph.microsoft.com/v1.0/users/{user-id | user-principal-name}/teamwork/installedApps?\$expand=teamsApp\(\$select=id)&\$filter=contains\(teamsApp/id,'{teams-app-id}')' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer XXXX'

Example response:

{
    "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('xxxx.xxxx@company.com')/teamwork/installedApps(teamsApp(id))",
    "@odata.count": 1,
    "value": [
        {
            "id": "{teams-app-installation-id}",
            "consentedPermissionSet": null,
            "teamsApp": {
                "id": "{teams-app-id}",
            }
        }
    ]
}

The ‘teams-app-installation-id’ is needed to retrieve the Chat ID:

curl --location 'https://graph.microsoft.com/v1.0/users/{user-id | user-principal-name}/teamwork/installedApps/{teams-app-installation-id}/chat' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer XXXX'

If the first call indicates no instance exists, wait a moment and try again. Once successful, you’ll receive a JSON response containing the ‘id’ attribute, which is the Chat ID. Note that if the app is uninstalled and reinstalled, the Chat ID will change, so it’s good to automate this process.

Testing the Bot

To test, send a request to the /api/adaptive-card endpoint, which should return an Adaptive Card JSON. You can then send it to the chat using the following query:

curl --location 'https://{web-app-name}.azurewebsites.net/api/adaptive-card' \
--header 'Content-Type: application/json' \
--data-raw '{
  "chatId": "{chat-id}",
  "serviceUrl": "https://smba.trafficmanager.net/teams/",
  "adaptiveCardDefinition": {
      "type": "AdaptiveCard",
      "version": "1.2",
      "body": [
        {
          "type": "TextBlock",
          "text": "Hello, I am working!",
          "weight": "Bolder",
          "size": "Medium"
        }
      ]
    }
}'

If successful, you’ll receive a ‘200 OK’ response and the Adaptive Card JSON. First successfully send adaptive card

If it’s the first user or dependencies haven’t fully propagated, you might see the following error:

{
    "error": "Failed to send adaptive card.",
    "details": "The bot is not installed in the chat."
}

In such cases, wait for propagation and try again. Sometimes, this process can take a while (even up to a day), but once everything is set up, the bot should work without issues.

If you’ve reached this point, you’ve almost completed the setup. You now have a bot capable of sending messages to Microsoft Teams. The final step is to create a test Fabric Notebook that generates Adaptive Card JSON and sends it to the Web App, which then delivers the card to the chat via the bot.

Fabric Notebook

Let’s add another scenario that is very user-friendly thanks to the cards. In this case, the user could be a transformation flow administrator. If we only wanted to send a notification that the orchestration of transformations has completed, a Python Notebook with code like this would suffice:

import requests
import json
import datetime

# This can be a cell with parameters
chat_id = {chat-id}
web_app_name = {web-app-name}

endpoint_uri = f"https://{web-app-name}.azurewebsites.net/api/adaptive-card"

def send_api_request(method, url, headers, data=None, response=True):
    call = requests.request(method, url=url,headers=headers,json=data)
    if response:
        return call.json()
    else:
        return call

def generate_adaptive_card(current_time) -> dict:
    formatted_time = current_time.strftime("%A, %d %B %Y at %H:%M:%S")

    card = {
        "type": "AdaptiveCard",
        "body": [
            {
                "type": "TextBlock",
                "text": "📅 Your pipeline has been refreshed",
                "weight": "Bolder",
                "size": "Large",
                "wrap": True,
                "color": "Accent"
            },
            {
                "type": "TextBlock",
                "text": f"Load ended at: **{formatted_time}**",
                "wrap": True,
                "spacing": "Small",
                "fontType": "Default"
            }
        ],
        "actions": [
            {
                "type": "Action.OpenUrl",
                "title": "Check your fresh data",
                "url": "https://adaptivecards.io"
            }
        ],
        "version": "1.4"
    }

    return card
    
datetime_now = datetime.datetime.now()

headers = {
    'Content-Type': 'application/json',
    'Accept': 'application/json'
}
payload = {
    "chatId": chat_id,
    "serviceUrl": "https://smba.trafficmanager.net/teams/",
    "adaptiveCardDefinition": generate_adaptive_card(datetime_now)
}
send_api_request("POST",endpoint_uri,headers,payload,False)

Here’s what the result of such a Python notebook would look like: Result of Orchestration pipeline

Here’s a slightly more complex version that considers identified errors during the transformation flow and sends them directly to the administrator so they know what to expect. The button also adapts, either redirecting the user to the Microsoft Fabric Monitor or to a specified URL, which can also be a parameter.

import requests
import json
import datetime

# This can be a cell with parameters from Data Pipeline (for example)
chat_id = ""
web_app_name = ""
error_messages = []

endpoint_uri = f"https://{web-app-name}.azurewebsites.net/api/adaptive-card"

def send_api_request(method, url, headers, data=None, response=True):
    call = requests.request(method, url=url, headers=headers, json=data)
    if response:
        return call.json()
    else:
        return call

def generate_adaptive_card(current_time, error_list=None) -> dict:
    formatted_time = current_time.strftime("%A, %d %B %Y at %H:%M:%S")
    error_count = len(error_list)
    has_errors = error_list and error_count > 0
    
    card_body = [
        {
            "type": "TextBlock",
            "text": ("⚠️ " if has_errors else "📅 ") + "Your pipeline has been refreshed" + (f" with {error_count} issues" if has_errors else "✅"),
            "weight": "Bolder",
            "size": "Large",
            "wrap": True,
            "color": "Attention" if has_errors else "Accent"
        },
        {
            "type": "TextBlock",
            "text": f"Load ended at: **{formatted_time}**",
            "wrap": True,
            "spacing": "Small"
        }
    ]

    if has_errors:
        card_body.append({
            "type": "TextBlock",
            "text": "Following errors occurred during execution:",
            "weight": "Bolder",
            "color": "Warning",
            "wrap": True,
            "spacing": "Medium"
        })

        card_body.append({
            "type": "Container",
            "items": [
                {
                    "type": "TextBlock",
                    "text": f"• {error}",
                    "wrap": True,
                    "spacing": "None"
                } for error in error_list
            ]
        })

    card = {
        "type": "AdaptiveCard",
        "body": card_body,
        "actions": [
            {
                "type": "Action.OpenUrl",
                "title": "Check details" if has_errors else "Check your fresh data",
                "url": "https://app.powerbi.com/monitoringhub?language=en-US&experience=fabric-developer" if has_errors else "{YOUR-URI}"
            }
        ],
        "version": "1.4"
    }

    return card

datetime_now = datetime.datetime.now()

headers = {
    'Content-Type': 'application/json',
    'Accept': 'application/json'
}

payload = {
    "chatId": chat_id,
    "serviceUrl": "https://smba.trafficmanager.net/teams/",
    "adaptiveCardDefinition": generate_adaptive_card(datetime_now, error_messages)
}

send_api_request("POST", endpoint_uri, headers, payload, False)

If any errors occur, the administrator will receive a chat message like this: Errors in Orchestration pipeline

Adaptive Cards allow you to display all kinds of information and actions, making them extremely versatile and adaptable to your needs. They can include images like PNG, JPG, GIF, or even SVG, either hosted externally or embedded directly in the card declaration as base64 or utf-8, for example:

{
    "type": "Image",
    "url": "data:image/png;base64,{base64-encoded-image}"
}

I mention this because it allows you to embed charts and visualizations into the card. HOWEVER, keep in mind that there is a size limit for cards, which is 25KB. If you have large images, it’s better to host them externally and include them in the card as a URL. Adaptive Cards also support Rich Text, so you can include HTML tags and text formatting in the card. However, be cautious, as Adaptive Cards are not full-fledged HTML and CSS, so some tags may not work or will be ignored.

Additionally, if you decide to use SVG in an Adaptive Card and create them inefficiently, such as by converting from a Plotly chart, you will almost certainly exceed the capacity limit. If you build the SVG yourself, you can render even very complex charts and visualizations by using efficient and simpler code. A second warning regarding SVG is that they WILL NOT RENDER on mobile in Adaptive Cards! Therefore, you need to convert them to PNG, which will impact any dynamic elements you might have in SVG (animations, etc.). For example, using the Python library cairosvg, which is not natively part of Microsoft Fabric Runtimes but can be installed. Here’s a small example of converting SVG to PNG:

%pip install cairosvg
import cairosvg
import base64

# Create an SVG string
svg_initial = "<svg xmlns='http://www.w3.org/2000/svg' width='100' height='100'>"
svg_circle = "<circle cx='50' cy='50' r='40' stroke='black' stroke-width='3' fill='red'/>"
svg_end = "</svg>"
svg_content = svg_initial + svg_circle + svg_end

# Encode SVG code to base64
png_bytes = cairosvg.svg2png(bytestring=svg_content.encode('utf-8'))
encoded_png = base64.b64encode(png_bytes).decode('utf-8')

# Create a data URL for the PNG image
png_data_url = f"data:image/png;base64,{encoded_png}"

Using the same approach, I prepared an Adaptive Card that helps monitor and track when data loading from a source fails. What was the source? A Lakehouse, where I queried a view with refresh data filtered for the last 30 days. I created an SVG chart, converted it to PNG, and embedded it into the Adaptive Card. The result looks like this: Adaptive Card with PNG/SVG Timeline

Of course, the data can come from anywhere:

Data Pipeline (as a parameter)
Lakehouse (T-SQL Endpoint or directly from files)
Warehouse (SQL Endpoint or directly from files)
Notebook (as a parameter)
Semantic Model (DAX Endpoint, XMLA Endpoint)
and so on.

It’s up to you how you leverage this capability.

Conclusion

In this article, we demonstrated how to create a simple bot for Microsoft Teams capable of sending messages to a chat. We showed how to create an Azure Web App, Azure Bot, and Microsoft Teams App. We also explained how to obtain a Chat ID and, finally, how to create an Adaptive Card and send it to a chat using a Python notebook. I hope this article was helpful and that you learned something new. If you have any questions or comments, feel free to reach out.

Write your data into Lakehouse by Notebooks

Mon, 10 Mar 2025 10:00:00 +0100

Since Lakehouse is one of the key items within Microsoft Fabric, it is important to know how to write data into it in various formats and using different tools. One of the most common tools is notebooks, as they provide great flexibility and speed for development and testing with graphical outputs. In this article, I want to focus primarily on the following types of notebooks:

PySpark
Python

Both of these types can write data into Lakehouse items (with or without schema support), but each does it slightly differently and supports different approaches to achieve the goal. However, what they have in common is that both can have a default Lakehouse set, and all operations are performed against it by default. From a development perspective, it is good to have the ability to set the target Lakehouse dynamically, not just rely on one default, to easily switch between different environments and, in specific scenarios, write to multiple Lakehouses simultaneously.

This can be achieved using APIs, configuration, or referencing individual Lakehouses by their indexes in the environment. Before we dive into writing data, let’s look at how we can work with Lakehouse in Fabric and how we can easily switch between different Lakehouses.

Default Lakehouses in Notebook

In Fabric, it is possible to have only one default Lakehouse set within a notebook. This default Lakehouse is used for many operations as the primary calling element because it serves as the source Hive metastore. An example of this can be reading data:

# Read data from default Lakehouse - Table Customers
## loads a table from the default catalog and database configured in Spark
df = spark.read.table('Customers')
## explicitly loads a Delta table from a given file path (for us it is the default Lakehouse)
df = spark.read.format("delta").load('Tables/Customers')
## executes a SQL query on a table stored in a specific database/schema (without specifying the database, the default one is used)
df = spark.sql("SELECT * FROM Customers")

As you can see, there are quite a few ways to read data (these are not all, just examples). The undeniable advantage of default Lakehouses is that we never had to specify the path/database or anything else. Once you set a Lakehouse as default, everything is automatically performed against it, which simplifies work, shortens code, and increases readability. If all executed notebooks were pinned to the same Lakehouse with the same environment, we could benefit from High Concurrencies (as mentioned in Orchestration Fabric Notebooks).

On the other hand, if I am creating a solution that I want to transfer between environments (without using Deployment Pipelines), customers, scenarios, or even just sometimes migrate a Lakehouse, it is good to have the ability to dynamically change the target Lakehouse without having to open each notebook and manually reset the default Lakehouse. Because modifying, for example, 10 notebooks is repetitive and boring but with a low probability of error. If I have more notebooks, say 50, the risk of error increases, and it becomes very inefficient time-wise.

Switching Default Lakehouse by Configuration

Each notebook contains configuration metadata that indicates which Environment, Lakehouse the notebook should connect to upon session initialization. If the notebook contains the magic command ‘%%configure’ in the FIRST cell, these configuration metadata can be overwritten during session initialization.

%%configure -f
{ 
    "defaultLakehouse": {
        "name": '<name of the lakehouse>',
        "id": '<lakehouse id>',
        "workspaceId": '<workspace id of the lakehouse>'
    }
}

This variant is very simple and readable, but it is also very static and does not allow dynamic switching of Lakehouse within a single notebook during a single run. Since it is the first cell, dynamic switching options, such as passing parameters from outside (e.g., from orchestration using notebookutils.notebook.runMultiple), are very limited.

Switching Default Lakehouse by notebookutils

Another option is to use the mentioned notebookutils library. It allows performing many operations necessary for notebook development and orchestration. One of them is the ability to dynamically change the default Lakehouse of notebooks. This function is very simple, as it requires only a few basic parameters, and everything else is handled within the library.

# Update the definition of a Notebook
notebookutils.notebook.updateDefinition(
    name = "", # Notebook id or name
    workspaceId = "", # Workspace ID of notebook
    defaultLakehouse = "", # ID or name of new default Lakehouse
    defaultLakehouseWorkspace = "" # Workspace ID of new default Lakehouse
)

This function can be used in any cell and utilized within a loop to update all notebooks in a single run. This way, it is possible to easily and efficiently switch between different Lakehouses, increasing flexibility and development and testing possibilities. However, this still does not solve the problem of needing to write to multiple Lakehouses simultaneously or direct parameterization within a single run. It still holds that I must first make changes to all notebooks and only then can I start doing anything else.

In article Orchestration Fabric Notebooks I also shown, how to use notebookutils.notebook.runMultiple to run multiple notebooks in parallel and in DAG definition is attribute “useRootDefaultLakehouse”: True, which allows to use the default Lakehouse of the root notebook for all child notebooks. This is a very useful feature, especially when you need to run multiple notebooks in parallel and want to use the same Lakehouse for all of them.

Writing data into Lakehouse by PySpark

As mentioned above, writing data into Lakehouse can be done in multiple ways, just like reading. If I stick to the default Lakehouse for a moment, writing data into a Delta Table can be done as follows:

# Write data to default Lakehouse - Table Customers
## append or overwrites a table in the default catalog and database configured in Spark
df.write.mode("append").saveAsTable('Customers')
df.write.mode("overwrite").saveAsTable('Customers')
## append or overwrites the content of the DataFrame to a Delta table in the default catalog and database configured in Spark
df.write.format("delta").mode("append").save('Tables/Customers')
df.write.format("delta").mode("overwrite").save('Tables/Customers')
## executes an SQL query to write the content of the DataFrame to a table stored in a specific database/schema
df.write.insertInto("Customers")

However, if I want to write to different Lakehouses or have higher parameterization support, I need to use a slightly different approach. Instead of referring to the default Lakehouse, I refer directly to the specific one I want to use. Since “save” supports saving using ABFSS path, this is very simple. It is good to know that within Microsoft Fabric, you can encounter two ABFSS path formats, both interchangeable and functioning the same:

abfss://{workspace id}@onelake.dfs.fabric.microsoft.com/{lakehouse id}/Tables/{db schema}/{table name}
abfss://{workspace_name}@onelake.dfs.fabric.microsoft.com/{lakehouse_name}.Lakehouse/Files/{folder_name}/{file_name}.{file_extension}

The entire code can look as follows:

# Write data to specific Lakehouse - Table Customers
lakehouse_id = '<lakehouse id>'
lakehouse_workspace_id = '<workspace id of the lakehouse>'
db_schema = '<database schema>' # Needs to be specified only for Lakehouses with schema support
table_name = '<table name>'
abfss_path = f"abfss://{lakehouse_workspace_id}@onelake.dfs.fabric.microsoft.com/{lakehouse_id}/Tables/{db_schema}/{table_name}"

## append or overwrites a table in specific Lakehouse
df.write.mode("append").format("delta").save(abfss_path)
df.write.mode("overwrite").format("delta").save(abfss_path)

This is a basic way to write to a Lakehouse outside the current workspace and not the default Lakehouse. Since this does not rely on direct notebook metadata, it is possible to parameterize this operation and connect to various IDs at runtime. However, there are more options…

For example, if we are talking about a Lakehouse within the same workspace, it is possible to use simpler methods like saveAsTable, where you can specify the Lakehouse name and table, or even schema.

lakehouse_name = '<lakehouse name>'
table_name = '<table name>'

df.write.mode("append").saveAsTable(f"{lakehouse_name}.{table_name}")
df.write.mode("overwrite").saveAsTable(f"{lakehouse_name}.{table_name}")

schema = '<db schema>'
df.write.mode("append").saveAsTable(f"{lakehouse_name}.{schema}.{table_name}")
df.write.mode("overwrite").saveAsTable(f"{lakehouse_name}.{schema}.{table_name}")

Of course, you can also use other packages that can help with writing. For example, delta.tables, which allows performing various operations on Delta Tables, such as writing, reading, updating values, deleting, optimizing, backing up, and much more. Since it is a Python package, it can be used within notebooks, simplifying and shortening the code.

from delta.tables import DeltaTable

lakehouse_id = '<lakehouse id>'
lakehouse_workspace_id = '<workspace id of the lakehouse>'
db_schema = '<database schema>' # Needs to be specified only for Lakehouses with schema support
table_name = '<table name>'
path = f"abfss://{lakehouse_workspace_id}@onelake.dfs.fabric.microsoft.com/{lakehouse_id}/Tables/{db_schema}/{table_name}"

deltaTable = DeltaTable.forPath(spark, path)
deltaTable.alias("df").merge(df.alias("s"), "df.id = s.id" ).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()

For writing data into Files, it works similarly, just instead of referring to “Tables,” you refer to “Files,” and instead of a table, you write directly to a file. This way, you can write data to a Lakehouse outside the workspace or within the workspace itself. The format of writing, the level of nesting, and other parameters can vary according to needs and requirements.

# Write data to specific Lakehouse - Table Customers
lakehouse_id = '<lakehouse id>'
lakehouse_workspace_id = '<workspace id of the lakehouse>'
folder_name = '<folder name>'
abfss_path = f"abfss://{lakehouse_workspace_id}@onelake.dfs.fabric.microsoft.com/{lakehouse_id}/Files/{folder_name}"

## write data to specific Lakehouse as files without specifying name
df.write.mode("overwrite").format("parquet").save(abfss_path)
df.write.mode("overwrite").format("csv").save(abfss_path)

## write data to specific Lakehouse as files with specifying name
file_name = '<file name>'
df.write.mode("overwrite").format("parquet").save(f"{abfss_path}/{file_name}.parquet")
df.write.mode("overwrite").format("csv").save(f"{abfss_path}/{file_name}.csv")

Writing data into Lakehouse by Python

If we talk about Delta tables again, writing here can be done using the deltalake library, which is very similar to the delta.tables library but is intended for Python.

from deltalake import DeltaTable, write_deltalake

lakehouse_id = '<lakehouse id>'
lakehouse_workspace_id = '<workspace id of the lakehouse>'
table_name = '<table name>'
path = f"abfss://{lakehouse_workspace_id}@onelake.dfs.fabric.microsoft.com/{lakehouse_id}/Tables/{table_name}"

write_deltalake(path, df, mode="overwrite")

Be aware of that, if you read data from a Delta Table into Python using DeltaTable, string columns are automatically extracted as Objects. Therefore, before saving them again, you need to cast them back to String, for example, using the following code:

df = df.astype({col: str for col in df.select_dtypes(include=['object']).columns})

If it is not prefered to use Pandas frames, it is of course possible to use other libraries such as Polars and use their methods for writing. For the mentioned Polars, it is the function write_delta, which allows writing to Lakehouse, requiring the path to the table, write mode, and other parameters.

import polars as pl

lakehouse_id = '<lakehouse id>'
lakehouse_workspace_id = '<workspace id of the lakehouse>'
table_name = '<table name>'
path = f"abfss://{lakehouse_workspace_id}@onelake.dfs.fabric.microsoft.com/{lakehouse_id}/Tables/{table_name}"

## append or overwrites (including schema overwrite)
pl_frame.write_delta(path, mode="overwrite", delta_write_options={"schema_mode": "overwrite"})
pl_frame.write_delta(path, mode="append")

For files, it is quite important to distinguish whether the planned function we want to use supports the use of ABFSS or only relative paths. For example, for Pandas, it is possible to use the function to_parquet, which supports ABFSS and allows creating directories as well.ting directories.

lakehouse_id = '<lakehouse id>'
lakehouse_workspace_id = '<workspace id of the lakehouse>'
folder_name = '<folder name>'
table_name = '<table name>'

path = f"abfss://{lakehouse_workspace_id}@onelake.dfs.fabric.microsoft.com/{lakehouse_id}/Files/{folder_name}/{table_name}.parquet"
pandas_frame.to_parquet(path)

On the other hand, the mentioned Polars, for example, for the function write_parquet needs to use a relative path. Respectively, it needs to be connected to the catalog and then write data to it. For such scenarios, it is easiest to use the mentioned default connection to Lakehouse because then you do not have to worry about paths and other parameters. However, if we want to write to another Lakehouse, we need to temporarily connect it within the given session and then write the data. For these temporary connections, it is easiest to use notebookutils.fs.mount.

 
lakehouse_id = '<lakehouse id>'
lakehouse_workspace_id = '<workspace id of the lakehouse>'
mount_name = '<mount name>'
folder_name = '<folder name>'
file_name = '<file name>'
extension = '<file extension>'
mounted_lakehouse = notebookutils.fs.mount(f"abfss://{lakehouse_workspace_id}@onelake.dfs.fabric.microsoft.com/{lakehouse_id}",mount_name) # Mount Lakehouse
mount_path = notebookutils.fs.getMountPath(mount_name) # Get the mount path
output_dir = f"{mount_path}/{folder_name}"
notebookutils.fs.mkdirs(output_dir) # Create directory if not exists
pl_frame.write_parquet(f"{output_dir}/{file_name}.{extension}") # Write data
notebookutils.fs.unmount(mount_name) # Unmount Lakehouse

Since mounting creates a connection to the given location, it is good to disconnect it again if it is no longer needed. On the other hand, you can wrap the mount in a class that will have methods for writing and disconnecting, which simplifies and clarifies the code. You can then open the connection, write all the files, and only then close the connection. This way, unnecessary repeated mounting and unmounting is avoided. The class can look like this:

class MountedWriter:
    def __init__(self, workspace_id, lakehouse_id, parent_folder_name):
        self.workspace = workspace_id
        self.lakehouse = lakehouse_id
        self.parent_folder_name = parent_folder_name
        self.mount_name = '/mnt/lakehouse'
        self.mount = notebookutils.fs.mount(
                f"abfss://{self.workspace}@onelake.dfs.fabric.microsoft.com/{self.lakehouse}",
                    self.mount_name
                )
        self.mount_path = ""
    
    def get_mounted_path(self):
        self.mount_path = notebookutils.fs.getMountPath(self.mount_name)
        return self.mount_path

    def check_or_create_existing_directory(self, folder_name):
        self.mount_path = self.get_mounted_path()
        output_dir = f"{self.mount_path}/Files/{self.parent_folder_name}/{folder_name}"
        notebookutils.fs.mkdirs(output_dir)

    def create_file(self,df_to_be_written, folder_name, file_name_parquet):
        self.check_or_create_existing_directory(folder_name)
        output_dir = f"{self.mount_path}/Files/{self.parent_folder_name}/{folder_name}"
        output_file = f"{output_dir}/{file_name_parquet}"
        df_to_be_written.to_parquet(output_file)

    def end_mounting(self):
        notebookutils.fs.unmount(self.mount_name)

Orchestration Fabric Notebooks

Fri, 11 Oct 2024 09:00:00 +0200

Let’s start by introducing what orchestration is and why it’s important to talk about shared resources. Orchestration is a discipline focused on managing and coordinating individual items or control elements to collectively manage the flow of our data operations. In the context of Fabric, this involves managing notebooks, dataflows, pipelines, stored procedures, semantic model updates, and many other items, activities, and services that may even be outside of Fabric.

Orchestration

Pipelines are often used for this task, helping to trigger these activities at the right time, thus aiding in the harmonization of the entire process to avoid unnecessary delays or triggering activities that might not have the correct inputs (e.g., missing data from previous transformations).

If such harmonization does not occur, we might encounter situations like this. Disharmonized schedule of items

Individual process steps do not follow each other and can be triggered independently. If this is indeed the case, they can be run independently using their own timers. However, if the steps follow each other and depend on each other, they need to be triggered in a specific order and with specific inputs. This scenario might look like this after adding dependencies. Item Continuities

In such a case, it is necessary to ensure that these activities are also triggered at the correct time. This is exactly where Pipeline or Apache Airflow can be used. They allow us to define individual steps, when they should occur, and their dependencies. Often, we can also pass input parameters that can be used during execution. The result of harmonization within orchestration might look like this.ion might look like this. Harmonized schedule of items

Such a harmonized process can then be triggered and easily monitored to ensure everything occurred in the correct order, with expected results, and potentially avoid triggering subsequent transformation steps or initiate a restart of a previous step in case of an error, and only then automatically trigger the dependencies.y then automatically trigger the dependencies.

Besides the two mentioned options (Pipeline and Apache Airflow), other options can be used for orchestration. These include using API or Notebooks themselves. Why and when we should use them will be discussed later in this article. But to make sense of it, we need to introduce what shared resources are.

Shared resources

The first clear perspective is that within Microsoft Fabric capacities, we have a limited number of CU (Capacity Units) available at any given moment. These CUs are directly proportional to the capacity we have purchased ~ Microsoft Fabric Licenses. Table of Fabric SKUs

CUs are thus a unit that tells us how many resources we can use at any given second. If we exceed them, delays, slowdowns, or even rejection of new inputs into the system occur ~ Throttling limits.

This first fact must be kept in mind. It applies to all items within Microsoft Fabric capacity, regardless of whether we are talking about background or interactive operations. The second point, which is extremely critical from the perspective of notebooks, is the limit on how many pyspark sessions can run simultaneously within our capacity. This limit can be particularly unpleasant for beginner developers or companies just starting with Microsoft Fabric.

Why? Because if, for example, we have four developers currently creating and testing their notebooks, it can easily happen that a timed notebook does not get space in the capacity and has to wait for space to free up or even be rejected. The same can happen to another developer who needs to work at that moment.

To better understand this limit, we need to look at another table prepared for us in Microsoft Learn regarding Spark concurrency limits. Spark VCores provided based on SKU

The table clearly shows that depending on the SKU, Spark VCores (1 CU = 2 Spark VCores) are provided for our use. It also shows that there is something called a Queue. The Queue is essentially a line that tells us how many notebooks that cannot be run at that moment are waiting for space to free up and will be automatically triggered once space is available. Since the whole process works on the FI-FO (First In - First Out) method, it is important to remember that they will be triggered in the exact order they were added to the queue, and if the queue is full, they will be rejected. Therefore, it is important to properly manage when and how notebooks should be triggered, and if, for example, a pipeline should trigger 5 parallel notebooks, it is quite possible that they will not fit into the available resources and will start returning this type of error.

HTTP Response code 430: This Spark job can't be run because you have hit a Spark compute or API rate limit. To run this Spark job, cancel an active Spark job through the Monitoring hub, or choose a larger capacity SKU or try again later.

Spark Sessions

To better understand Sessions and their capacity consumption, it is important to know that for utilizing the overall Apache Spark Compute, there are Spark Pools, which execute the instructions of our transformations within sessions. These sessions are essentially individual instances that contain all the necessary information for running our notebooks. This information includes, for example, Spark settings, libraries we have installed, or settings for connecting to data sources. All this information is stored within a single session and is available to all notebooks that are run within this session. We can divide pools into two basic types:

Starter Pools
Custom Pools

The main difference between them is that Starter Pools represent the default layer that can be used for sessions within Microsoft Fabric. These clusters are always active and can therefore be used almost immediately. It is possible to set a different number of nodes for them, which can provide for their sessions, and here we are basically talking about 1-10 nodes. Custom Pools, on the other hand, represent the possibility to set your own specifics that will be used for incoming sessions. They can be customized in terms of the size of the nodes, autoscale, and executor allocation. However, they are not always active and need to be started before they can be used. This can take a while, so your session may not be available immediately. (Here you can find out, how in case of billing these pools works ~ Billing and Utilization)

It is very important to mention that we have different types of sessions. Classic ones, where one notebook uses one session. It is also possible that one user can have multiple sessions because they needed more notebooks during development and forgot to turn off the session in individual notebooks. Yes, one user can very easily use up the capacity for the entire company. However, there are High concurrency sessions. These allow sharing a session among multiple notebooks at once. This is very useful when we want to be considerate of other users and processes. To be able to share a session, it is necessary to follow the rules mentioned in the documentation:

Be run by the same user.
Have the same default lakehouse. Notebooks without a default lakehouse can share sessions with other notebooks that don’t have a default lakehouse.
Have the same Spark compute configurations.
Have the same library packages. You can have different inline library installations as part of notebook cells and still share the session with notebooks having different library dependencies.

These High concurrency sessions are also not a panacea and need to be manually started in the respective notebooks when we want to work with them. To be able to use them at all, they need to be enabled within the workspace. Workspace High Concurrency Settings

Orchestration of Notebooks

If we were to talk about a corporate environment where it is necessary to run, for example, a pipeline for orchestrating more, then besides harmonizing activities, we also need to talk about harmonizing resources and individual runs that use these resources. Of course, it is possible within, for example, one pipeline to set it up so that individual notebooks are triggered sequentially, ensuring that only one session is used at any given moment. However, it sometimes happens that it takes a while for a session to free up, and meanwhile, another one is already being triggered. Nowadays, this is not so common, but in the early public preview phase of Microsoft Fabric, this happened relatively often. At that moment in time, there were two sessions. This approach is quite valid today, but CAUTION!

Switching sessions leads to other problems. Each session that starts needs to add all resources and settings to itself. This in itself takes initialization time, and if one session ends, it needs to be cleaned up. Adding to this, if another notebook starts in the meantime, either waiting in the queue or arriving 0.01ms earlier, potential delays and problems start to accumulate.

In the above settings, it is already possible to use High concurrency sessions within pipelines. This also helps solve the whole problem. However, it is important to remember that all notebooks in such a case must share the exact same settings for Spark compute, use the same libraries, etc., according to the documentation. The pipeline then decides whether to attach the notebook to the session or not.

If we want to have a higher certainty that the notebook will attach to the session, we can use a slightly different approach to the whole problem. We can use a Notebook that will trigger other notebooks within its session. This notebook can manage which notebooks will be triggered in parallel or sequential order. So, very similar to how it can be achieved within a Pipeline. To do this, we need to use a special package called notebookutils. It contains many useful functions for working with the Microsoft Fabric environment.

NotebookUtils for orchestration

From runtime version 1.3, it is not necessary to install this package because it is part of the environment by default. For lower versions, it needs to be installed using the following code.

import notebookutils

Within this package, there is a module called notebook. As the name suggests, it focuses on operations with notebooks. Besides functions like create(), update(), delete(), there are three functions that should interest us for orchestration. These are run(), runMultiple(), and validateDAG(). We will get to the last one later.

Functions for orchestration

Let’s start with the run() function. As the name suggests, this function serves to trigger one notebook. This function has several parameters that need to be provided. The first is the name of the notebook we want to trigger. The second is the timeout in seconds, which determines the time after which the notebook run will be terminated for exceeding the time limit. The default value is 90 seconds. The third is the option to pass parameters that we want the notebook to use. The last is workspaceId, because from version 1.2, it is possible to trigger notebooks in other workspaces than the one where the triggering notebook is located using this package.notebook is located using this package.

notebookutils.notebook.run("notebook name", <timeoutSeconds>, <parameterMap>, <workspaceId>)

A notebook triggered this way will be on the same session as the notebook that triggered it. We are thus using similar behavior as with High concurrency sessions. If we wanted to trigger notebooks sequentially but using the same session, we could use this function and pass it the individual notebooks we want to trigger. If it were a lower number of notebooks, this is quite a valid solution. However, if we wanted to trigger notebooks in parallel or generally wanted to trigger more and properly manage their dependencies, it is more suitable to use the runMultiple() function.

notebookutils.notebook.runMultiple(<DAG>, <config>)

It accepts either a list of notebooks to be triggered in parallel mode or by providing a complex data structure (dict or JSON string). Subsequently, it is possible to define a certain form of configuration parameters that can affect the output values. Specifically, these are displayDAGViaGraphviz, showArgs, and showTime. The first can be used to display a graph showing the dependencies between individual notebooks defined through the mentioned complex structure and also represents whether the notebook was successfully executed. The second and third can be used to display the arguments passed to individual notebooks and the execution time. Both of these parameters are tied to the graph that is to be displayed. The default value for all these parameters is False.

%run magic command:

Besides the mentioned functions, there is also the option to use the magic command %run. This also triggers another notebook. You can read more details in the link, including a warning that nesting more than 5 levels of notebooks will return an exception. However, within this run, all resources are shared because there is no physical triggering of another notebook but embedding the given notebook into the current one. This means that if the embedded notebook contains some libraries or variables, they will also be brought into the triggering notebook and can cause collisions and overwriting. This can be especially dangerous if we want multiple developers to develop these notebooks. The probability of collision would be very high, also because one developer might use a different default lakehouse than the triggering one.

%run [-b/--builtin -c/--current] [script_file.py/.sql] [variables ...]

Supported DAG for Notebooks

DAG stands for Directed Acyclic Graph. This represents a form of graph where individual nodes of this graph are directed towards other nodes and structured to avoid cycles. Every node can be in graph exactly once. In the context of notebook orchestration, this graph can be used to manage dependencies between individual notebooks. Since individual operations can always be performed in a certain common sequence or parallelism. Similarly, it is true that we do not want to perform the same operation multiple times.

Will be also great to mention, that the end of a graph doesnt need to be just one. DAG can have multiple ends in multiple branches.

This form of graph is supposed to help us establish the correct order in which notebooks should be triggered and potentially prevent triggering notebooks that depend on a notebook that has not yet been triggered or ended with an error and we do not have valid data. Under certain conditions, it may of course be desirable to trigger notebooks even if one of them ended with an error, and yes, this special scenario can be handled, but it is important to remember that it can cause other problems.

The expected DAG can be defined in two possible ways, as mentioned. Either as a list of individual notebooks to be executed. Since it is a simple list, there is no dependency, and they are triggered in parallel. Or it can be defined as a complex data structure, for example, a JSON string that will define individual dependencies and rules. DAG is not only used within Microsoft Fabric but is also part of the previously mentioned Apache Airflow. In any case, within the implementation used for Notebooks in Microsoft Fabric, we have quite a few useful attributes that can be used within the DAG definition.

Example of DAG code, which can be obtained using notebookutils.notebook.help(“runMultiple”):

DAG = {
    "activities": [
        {
            "name": "notebook1", # activity name, must be unique
            "path": "notebook1", # notebook path
            "timeoutPerCellInSeconds": 90, # max timeout for each cell, default to 90 seconds
            "args": {"param1": "value1"}, # notebook parameters
            "workspace": "workspace1", # workspace name, default to current workspace
            "retry": 0, # max retry times, default to 0
            "retryIntervalInSeconds": 0, # retry interval, default to 0 seconds
            "dependencies": [] # list of activity names that this activity depends on
        },
        {
            "name": "notebook2",
            "path": "notebook2",
            "timeoutPerCellInSeconds": 120,
            "args": {
                "useRootDefaultLakehouse": True, # set useRootDefaultLakehouse as True
                "param1": "@activity('notebook1').exitValue()" # use exit value of notebook1
            },
            # to ignore that child notebook attach a different default lakehouse.
            "retry": 1,
            "retryIntervalInSeconds": 10,
            "dependencies": ["notebook1"]
        }
    ],
    "timeoutInSeconds": 43200, # max timeout for the entire pipeline, default to 12 hours
    "concurrency": 50 # max number of notebooks to run concurrently, default to 50, 0 means unlimited
}

notebookutils.notebook.runMultiple(DAG, {"displayDAGViaGraphviz": False, "showArgs": False, "showTime": False})

The example shows the default values set if the developer does not provide them. Besides, for example, input parameters passed within the args attribute, it is possible to use special functions available within this definition. These include @activity(“nameOfNotebook”).exitValue(). This function returns the value returned by a notebook that has already been triggered. Given that you may often need more time for processing or obtaining your data using notebooks, note the timeoutPerCellInSeconds attribute, which, as the name suggests, sets the default for internal cells of the triggered notebook. The default value is 90 seconds, which may be very little for certain operations (e.g., downloading data from asynchronous APIs).

Now to the really important part. This is the arrangement of notebooks in the order of individual nodes, or in our case, notebooks. Within the graph. The attribute dependencies is used for this within the supported structure. It determines on which notebook the current notebook depends and must be executed only after the previous one has been executed.

"dependencies": ["notebook1"], #  It can be run after a successful run of notebook 'notebook1'.
"dependencies": ["notebook1","notebook2"] # It can be run after a successful run of both notebooks 'notebook1' and 'notebook2'.
"dependencies": [] # It can be run anytime, usually in the layer of initials notebooks.

These examples can be represented as following chart:

It is also important to note that there is a default value for concurrency at the global level. This tells us how many notebooks can run in parallel, provided we have sufficient resources (Excerpt from the documentation on Microsoft Learn: The parallelism degree of the multiple notebook run is restricted to the total available compute resource of a Spark session.) The documentation clearly states that it is not good to exceed the default value of 50, as it leads to instability and performance issues due to high performance demands.

Example of executed DAG with the graph displayed:

DAG = {
    "activities": [
        {
            "name": "NotebookThatAddsAValue", 
            "path": "NotebookThatAddsAValue", 
            "timeoutPerCellInSeconds": 90,
            "args": {"myAwesomeParameter": 300}, 
        },
        {
            "name": "SecondNotebookToBeRun",
            "path": "SecondNotebookToBeRun",
            "timeoutPerCellInSeconds": 120,
            "retry": 1,
            "retryIntervalInSeconds": 10,
            "dependencies": ["NotebookThatAddsAValue"]
        },
        {
            "name": "SecondNotebookToBeRunButInParallel",
            "path": "SecondNotebookToBeRunButInParallel",
            "dependencies": ["NotebookThatAddsAValue"]
        },
        {
            "name": "LastNotebookToRun",
            "path": "LastNotebookToRun",
            "dependencies": ["SecondNotebookToBeRun","SecondNotebookToBeRunButInParallel"]
        },
        {
            "name": "api_caller",
            "path": "api_caller",
            "workspace": "3f72c575-7017-49a4-82d2-a96aa7702cc8",
            "dependencies": ["SecondNotebookToBeRunButInParallel","LastNotebookToRun"]
        }
    ],
    "timeoutInSeconds": 43200,
    "concurrency": 50
}
notebookutils.notebook.runMultiple(DAG, {"displayDAGViaGraphviz": True, "showArgs": True, "showTime": True})

Successful run with DAG Graph

In the image, you can see that one notebook returns some output value. As mentioned earlier, notebooks can return some output. This can be further processed or passed to other notebooks. To make a notebook return some output, you need to use the following function:

notebookutils.notebook.exit("String that you want to return")

This function not only returns a value but also closes the given notebook. If it contained more cells, they would not be executed. It is important to note that if a notebook is terminated this way, it always returns as “successfully” completed. Therefore, you need to handle Exceptions properly to decide when to return exit and when an actual exception should occur, preventing further graph traversal. If an exception occurs, the output looks as follows. Failed run

As I mentioned, sometimes even if a notebook fails, we still want to continue with the computation. It could be a lookup table that doesn’t change often or isn’t needed in further transformations and can be run later. In such cases, it is good to handle the possible output of such a notebook, for example, using try … catch.

try:
    spark.read.parquet("") # Will cause an error
except Exception as e:
    notebookutils.notebook.exit(f"Error occurred: {e}")

The graph traversal will continue because the output in case of an error is still the exit() function, which, as mentioned, returns a successful output from the notebook.

Fully executed graph with caught error inside

DAG Validation

When creating a DAG, especially a more complex form, it is good to verify that the form is correct and does not contain errors. This can be achieved using the validateDAG() function.

notebookutils.notebook.validateDAG(DAG)

This function accepts our created definition as a parameter. If everything is fine, it returns True. If there is an issue with provided DAG, it prints the error that was found.

Error in DAG definition

Conclusion

Orchestration of notebooks within Microsoft Fabric is very important and needs to be managed correctly. It is essential to keep in mind that we have limited resources in terms of CU capacity and notebook concurrency. Notebooks can be managed by various items and can even manage themselves. Managing notebooks through Pipelines is visually very simple and user-friendly but can lead to problems where we cannot correctly manage dependencies and utilize session sharing. Even High concurrency sessions will not always be the answer due to the many limitations that apply to them. A possible suitable orchestration is to use a DAG within the runMultiple() function. This function allows us to manage dependencies between notebooks and use available resources more efficiently.

Within the DAG, it is essential to handle notebook outputs and possible errors that may occur correctly. If we want to use some output from a notebook, we need to use the notebookutils.notebook.exit() function and handle errors using try … catch. Thanks to the DAG definition, it is possible to run notebooks in other workspaces, allowing us to create a master notebook that will manage the entire graph traversal and keep track of which notebook was run and which was not. This way, we can manage the overall computation of the Silver layer and correctly time individual operations in sequence.

GraphQL in Microsoft Fabric

Fri, 12 Jul 2024 11:00:00 +0200

It is an alternative to REST API and enables users to fetch data from multiple sources using a single query. Compared to REST API, GraphQL is much more flexible and allows users to retrieve only the data they need, reducing the amount of data transferred between the client and server. It also uses a single endpoint, reducing the number of requests made to the server. It is a platform and programming language-independent specification, meaning it can be used with any language and on any platform.

GraphQL is defined by an API schema written in the GraphQL schema definition language. Each schema specifies the types of data that users can request or modify, and the relationships between these types. The term “resolver” is often mentioned in relation to GraphQL. It refers to a function or functions responsible for fetching data for a specific field in the schema and provides instructions for converting the GraphQL operation into data. The operations supported by GraphQL are:

Query for querying data
Mutation for modifying data (CREATE, UPDATE, DELETE + Custom operations)
Subscription for subscribing to real-time data. It is a way to push data from the server to the client when the data changes.

Within the defined data types, it is possible to define relationships between them. Relationships can be one-way or two-way and can be defined using keys or custom functions. Additionally, custom data types that are not part of the GraphQL schema can be defined. GraphQL also supports functions that allow filtering, sorting, and pagination of data, meaning users can retrieve only the data they need and do not have to fetch all the data at once. However, it is necessary to always define all the fields that you want to retrieve. There are no wildcards like “*“ that would return all fields.

Supported Fabric Items

Within Microsoft Fabric, GraphQL can be used to query the following Fabric items in specific modes based on the supported types of operations.

Item	Types of operations
Warehouse	READ / WRITE
Lakehouse (SQL Analytics Endpoint)	READ
Mirrored Database (SQL Analytics Endpoint)	READ
Datamart	READ

This means that currently only Warehouse supports modifying data using GraphQL, making it the only item that supports Mutation operations. These operations must be explicitly defined within the GraphQL schema. However, this is currently automatically generated based on the schema of the respective item and cannot be manually modified. This ensures a consistent approach to data access and prevents errors in querying. Warehouse also automatically generates create mutations. Update and Delete mutations are currently not generated, but this functionality can be achieved through stored procedures. If a stored procedure exists within Warehouse, the corresponding GraphQL endpoint can generate a mutation for executing it, serving as a replacement for the missing Update and Delete mutations.

A single GraphQL item can be used to query multiple Warehouse, Lakehouse, Mirrored Database, and Datamart items simultaneously. However, it is not possible to establish relationships between them as defined in the GraphQL schema. Relationships always work within a single item.

GraphQL is now part of Microsoft Fabric in the context of Data Engineering, where it can be enabled in the Admin portal. It can be configured for specific sets of users who then have the ability to create it. Tenant settings - API for GraphQL

This setting can also delegate rights to individual capacity admins to potentially disable this feature within their capacity. Currently, it is not possible to limit GraphQL to specific items, meaning that if GraphQL is enabled, it is enabled for all items available within Fabric for a given author and does not have to be limited to the same workspace.

Connecting Data Sources to GraphQL

After creating a GraphQL item, the user is redirected to a canvas that prompts them to connect to available items (Warehouse, Lakehouse, Mirrored Database, and Datamart). Blank GraphQL canvas

In this canvas, the user can select individual tables to be used within the GraphQL item. This automatically generates the schema and, if applicable, mutation operations for those tables. Note: Relational relationships created within the item are not transferred to the schema and must be defined manually later. Table selection from Datamart

Depending on the item, different naming conventions for schemas and supported stored procedures are displayed. Selection from Warehouse with two schemas and one stored procedure

After connecting to the data, the original canvas is divided into multiple sections - Schema Explorer, Query Editor, Query Variable Editor, and Query results.

Schema Explorer displays all available tables and schemas for the item.
Query Editor is used to write queries for data.
Query Variable Editor is used to define variables used in queries.
Query results display the results of data queries. GraphQL connected to Datamart

If the user selects a table from another data source with the same name as an existing table, an error would occur because the table name must be unique within the entire schema. In such cases, the user is alerted that the name is already in use and must choose a different name using the Resolve button. Name already in use

Each connected data source is metaphorically represented as a folder containing its tables within the Schema Explorer. Queries and Mutations are displayed together. Schema Explorer with multiple data sources

Therefore, it is important to be careful with naming conventions when creating tables to easily distinguish and use them in queries. Using a prefix may seem like a good idea, but it may complicate and make queries less clear. Fortunately, individual tables can be renamed later! Options of tables

The author of the GraphQL item has the ability to remove tables or specific columns from the schema. This can be useful when not all columns of a table are needed in data queries. This can be done through the ellipsis icon “…“ next to the column name > Remove from Schema.

Creating Relationships Between Tables

It is possible to create relationships between tables manually within the Query Editor. Relationships can be one-way or two-way and can be defined using keys. They can only be created between tables from the same data source. One-way relationships mean that when querying one table, you can retrieve related records from the other table. In the case of two-way relationships, the same relationship extension is applied to the table that the relationship is referring to, but in reverse. If this setting is not enabled, it would mean that you can retrieve data referring from one table to the other, but not vice versa.

Unfortunately, in the current situation, it is necessary to create this relationship manually twice through the ellipsis icon “…“ next to the table name > Manage relationships > New relationship. This is where the relationship between tables can be defined. Defining relationship

After saving the relationship, a relationship extension is automatically created in the schema, which can be used in data queries, and additional relationships can be created. For example, creating the exact reverse relationship to achieve bidirectionality. Dual Relationship

Relationships are then displayed in the Schema Explorer as additional fields with an icon representing the relationship to another table. Relationship Column in Schema Explorer

By default, this field is named after the table to which the relationship is created. However, this can be confusing from a semantic perspective. For example, if we have a table “Sales” and a table “Employees”, the field “Sales” in the Employees table would make sense because one employee can make multiple sales. But in the Sales table, the field “Employees” would be confusing because one sale can be attributed to only one employee. Therefore, it is possible to rename this field to make it more meaningful. Instead of “Employees”, it could be “Employee”. Even this small change can greatly improve the clarity and comprehensibility of the schema and subsequent data queries. Relationships

Querying Data

As mentioned before, when querying data using GraphQL, the operation used is Query. This operation is initiated with the keyword query, followed by the selection of tables and the desired columns within them. In the context of Microsoft Fabric implementation, columns are defined within the nested object items. The overall query syntax is very similar to the structure of JSON:

query{ # <--- Query initiation
    sales { # <--- Table selection
        items{ # <--- Declaration of columns to be retrieved
            ID # <--- Column selection
            Customer
            Note
            ProductId
            Quantity
        }
    },
    products { # <--- Another Table
        items{
            ID
            Title
            Note
        }
    }
}

Within a table, additional information can be requested besides the content:

endCursor: pagination token to retrieve more records
hasNextPage: boolean value indicating if there is another page of records
__typename: provides the original name of the declared type within the schema

Their usage can be helpful for implementing pagination or obtaining information about the data structure. However, to fully utilize pagination, it is necessary to understand how the token obtained from the endCursor is used. It can be used within functions that allow filtering and sorting data. Specifically, the functions after, filter, first, and orderBy. These functions are written within parentheses and can be combined. For example, if we want to filter data and retrieve only the necessary information, we need to construct the condition. However, symbols like < or > are not used here. Instead, keywords are used:

eq (equals)
neq (not equals)
gt (greater than)
lt (less than)
gte (greater than or equals)
lte (less than or equals)
isNull (is null)

query {
    sales(filter: { ID: { eq: 50 } }) { # <--- Filtering only to records with ID 50
        items {
            ID
            Customer
            Note
            ProductId
            Quantity
        },
        endCursor, # <--- Receive pagination token
    }
}

To implement pagination, you can use the after function with the previously obtained token. However, if the previous query contained a function like (first: 5), it is recommended to use it again when using the next token. Otherwise, you may retrieve data in a larger range than the original query.

query {
    sales(first: 5, after: "######" ) { # <--- Receive only the next 5 records (###### replace with token)
        items {
            ID
            Customer
            Note
            ProductId
            Quantity
        }
    }
}

If you want to query data through relationships between tables, you first need to declare the columns within items and use the linking column. You must define the columns from the second table that you want to retrieve. However, there is a difference in syntax between the 1:M side and the M:1 side of the relationship.

# Sales - Employees (M:1)
# Employees - Sales (1:M)
query {
    sales {
        items {
            ID
            Quantity
            SaleDate
            employee { # <--- M:1 side of the relationship
                Title # <--- Will return a single record
            }
        }
    },
    employees {
        items {
            ID
            Title
            Role
            Salary
            sales { # <--- 1:M side of the relationship
                items { # <--- Will return an ARRAY of Sales records
                    SaleDate
                    Quantity
                }
            }
        }
    }
}

Nesting relationships is possible to any depth, but it may slow down the query. Therefore, it is important to consider whether it is necessary to nest relationships deeply and always request only the columns that are truly needed. Additionally, these relationships provide the option for nested filtering and sorting. It is possible to retrieve data from the “Employee” table based on data from the “Sales” table. For example, if we want to retrieve all salespeople who sold a specific product or sold more than 5 units in a single transaction.

query {
    employees(filter: { sales: { Quantity: { gt: 5 } } }) { # <--- Filtering only to Employees with at least one Sold Quantity greater than 5 in the Sales table
        items {
            ID
            Title
            Role
            Salary
        }
    }
}

Executing Mutations and Stored Procedures

Another very useful ability, as mentioned before, is the use of mutations and the execution of stored procedures. The beginning of the query is the same within the mutation operation. Since mutations are pre-defined within Microsoft Fabric, it is possible to refer to them directly. As mentioned before, within the Warehouse, mutations for creating new records are automatically generated. In case we want to create a new record in the Employees table, we can use the following query:

mutation {
    createEmployees(item: { ID: 100, Title: "Meerkat" }) {
        result # <--- Required field to return the result of the mutation
    }
}

Similarly, if we want to execute our own stored procedure, we need to know the name of the procedure or its mutation. Be careful to always provide all the input parameters required by the procedure.

mutation {
    executedeleteProduct(id: 100) {
        result
    }
}

If you want to modify the name of the procedure, you need to update the schema using the ellipsis icon “…“ next to the table name > Update schema > ellipsis icon “…“ next to the procedure name > Rename. However, this will not remove the “execute” prefix. It is automatically added to the procedure name in the schema definition. Rename Stored Procedure

Variables

Variables can be used within queries to make them more dynamic. They are defined within the Query Variable Editor and can be used within the query by referencing them with the $ symbol. Variables work in the same way as variables in any other language. Each variable needs to be declared with a name used to access its stored value. In GraphQL, variables are defined as an additional JSON, where attributes are used as variables. This can be useful when you want to filter data based on user input or when you want to reuse the same query with different parameters.

{
    "id": 100
}

It is still necessary to declare them within the desired operation and assign a data type, otherwise they would not be usable. However, once they are declared, they can be used in any query:

mutation variableFunction($id: Int!) { # <--- Declaration of the variable for the mutation purpose
    executedeleteProduct(id: $id) { # <--- Using the variable in the mutation
        result
    }
}

Similarly, they can be used in filters or in setting the number of records we want to retrieve:

{
    "id":360,
    "first": 10
}

query variableFunction($id: Int!, $first: Int!) { # <--- Declaration of the variables for the query purpose
    sales(first: $first, filter: { ID: { eq: $id } }) { # <--- Filtering only to records with ID equal to the variable and retrieving only the specific ammount records defined by the variable
        items {
            ID
            Customer
            Note
            ProductId
            Quantity
        }
    }
}

Conclusion

GraphQL is a powerful tool for querying data from multiple sources using a single query. It provides a flexible and efficient way to retrieve only the data needed, reducing the amount of data transferred between the client and server. It is a platform and programming language-independent specification, meaning it can be used with any language and on any platform. Within Microsoft Fabric, GraphQL can be used to query Warehouse, Lakehouse, Mirrored Database, and Datamart items. It is possible to create relationships between tables, query data, mutations, and executing stored procedures, and use variables to make queries more dynamic. GraphQL is a valuable addition to Microsoft Fabric, providing users with a powerful tool for querying data and enabling them to retrieve only the data they need.

Currently, GraphQL can only be used with a user token. Microsoft provided a tutorial on receiving it in the external application by logging into Microsoft Entra ID. Link to tutorial

Combining Calculation Groups

Tue, 16 Apr 2024 11:00:00 +0200

Users often have more of them within their solution or Semantic Models. Because of this, each calculation group has its own Prescendense, which determines the order of execution if several of them are selected simultaneously within one calculation. You must always have one calculation group with the same Prescendense. This behavior is necessary for the calculations to work correctly, so it is necessary to set the evaluation in the correct order. Sometimes, however, it is possible to get to a point where it would be necessary to somehow combine calculations from different calculation groups, which, under normal circumstances, are in a different order.

In the article Conditional Formatting with Calculation Groups, it was explained that functions of the SELECTEDMEASURE() type can be used within calculation items, which dynamically allow any measure to be used, with which the item comes into contact and its calculation to be extended or wholly changed. This principle can also be used to combine calculations from different groups of calculations. At the same time, in the article Field parameters in cooperation with Calculation groups, it was shown that it is not always necessary to use these functions, but that the user may need/want to use the calculation group, for example, to switch between different existing measures, which can also be achieved using field parameters. However, if we want to combine various linked modifications of calculations from different calculation groups, it is pretty practical to stick only to calculation groups and not field parameters.

How measure can be changed by Calculation Groups

The picture shows that the input measure [blank] is replaced with the measure [# Costs] using the first Calculation Group. The next Calculation Group then expands this measure, where thanks to the SELECTEDMEASURE() function, only the replaced measure from the first Calculation Group is transferred and causes it to display only the TOP 5 or BOTTOM 5 values, which can be used in visualizations. If the Range Parameter extends this calculation, users can also dynamically choose how many values they want to display. The numerical designation in the names of Calculation Groups is here for better orientation and to express their Prescendence.

Applied calculation groups on Bar chart

The advantage of this approach is that users can avoid creating new measures that would be unnecessary and could cause unnecessary complications within the Semantic Model. At the same time, it is a dynamic option where the mentioned TOP/BOTTOM calculation can be applied to essentially any input measure.

This type of calculation is very often used so that users have an easier orientation in the data and can focus more quickly on the most significant values. In such a case, however, it is beneficial to expand the visual with another option supporting the ability to absorb specific information. Thanks to colors, this is often done in Power BI. However, it depends a lot on the scenario currently being prepared for the end users. Sometimes, it may be necessary to show only the highest or lowest set values or those that exceed some limit or to build a gradient that supports visual perception.

In the article Multi-Color Gradient with DAX in Power BI, it was shown how DAX and its functions can be used to create the gradient just mentioned. What if we wanted to combine calculations from different groups and use the gradient simultaneously? For example, if the user selects TOP values, these values would be displayed with a green gradient, gradually lightening. If the BOTTOM value were selected, these values would be displayed with a red gradient. At that point, it would be easy to recognize which side we are looking at the data on and what the maximum or minimum is.

However, we run into the problem that such a calculation requires the ability to execute the given procedure, which comes from individual calculation groups. At the same time, however, we must comply with the conditions for conditional formatting of visual aspects such as Bars because otherwise it would not be possible to use them. An essential condition is that the inserted measure must return a color. It is more or less what and how it is defined. So, it can be a black color defined by the HEX color code. RGB, HSL, HSV, and so on can also be used. Another condition is that after all calculations or changes made using Calculation Groups, the output must still be a valid color. However, if we were to apply a general Calculation Group that is supposed to transform the input measure into the color that is to be used, at the same time, we wanted to use the previous Calculation Groups. We need to be aware that this new calculation group must always be at the end, but at the same time, it will also affect the calculation itself, not just the one that should explicitly return a color. So, both the measures used to return the values and the ones to return the color would return the same result. Suppose you thought it could be parameterized using the ISSELECTEDMEASURE() function. In that case, unfortunately, you will run into the problem that as the first Calculation Group overwrites the input measure, all subsequent SELECTEDMEASURE(), ISSELECTEDMEASURE(), and other similar functions will already get them explicitly measured from the first Calculation Group. So, the conditions that would be created would always be met or never not met.

A possible solution lies in a trick. First, we can block the first Calculation Group from working with another measure before we tell it (see the condition in the first image). So, if there is a collision between Calculation Group #1 and a measure that will be used within the conditional formatting, then nothing will happen. The second step is to create a Calculated Column in Calculation Group #1, which will contain one singular value that will be the same for all rows or Items. This value can be, for example, the value 1. If we then use the trick CALCULATED([blank], TREATAS({1}, ‘CG#1’[Column])) within the newly emerging Calculation Group #3, we achieve that Precedence 3 will call Precedence 1 and start the calculation from again. So #1 will be followed by #2 and #3, where it has to stop, so you need to make use of the condition that the calculation adjustment in #3 will only be made at a certain point in time, which is when it comes into contact with a measure for conditional formatting. The result of that calculation can be saved as a variable and even used as part of creating an auxiliary column in ADDCOULUMNS(), which we can save in a variable and then use.

Calculation Group #3 and Calculated Column and New measure

If I applied a new measure to the visual but not the mentioned Calculation Group #3, the chart would automatically change to black.

Bar chart with applied Conditional Formatting

When applying the mentioned Calculation Group #3, the gradient will also be applied. Additionally, using the automatic ORDINAL column from Calculation Group #2, the colors within the gradient will change if TOP or BOTTOM is selected. It is essential, however, that the slicers for #1 and #2 be set so that they are single-selection slicers. In this way, it will always be achieved that their values will be selected, and simultaneously, multiple simultaneous selections will not collide.

Custom Gradient-based on Measure from Calculation Group #1 and selection from Calculation Group #2 (BOTTOM)

So, if the user changes the value to TOP, the gradient will also change. As mentioned, this graph can also be extended with a Range Parameter, which would allow users to choose how many values they want to display.

Custom Gradient-based on Measure from Calculation Group #1 and selection from Calculation Group #2 (TOP)

Power BI Templates

Mon, 18 Dec 2023 10:00:00 +0100

But if we don’t even know that these other options exist, then it wouldn’t even occur to us to reach for them. In this article, I deliberately do not focus on “pseudo templates”, by which I mean, for example, DAX code templates for SVG generation, etc. However, if you are interested in some examples, you can find a few useful ones here.

But back to those that interest us and can help us create content faster that will be uniform within our organization or that will standardize the connection to some data source.

In such cases, most of us think of the mentioned “.pbit”, which I will come back to later, but files of the type “.pbix” and “.pbip” can serve just as well. But what about the often overlooked “.pbids” format? Let’s face it: who among us has ever created this file, and who regularly uses it… There are exceptions, but I’m often met with raised eyebrows when I mention this ending. The possibility of using templates directly in Power BI Service / Microsoft Fabric to facilitate the production of reports in an online environment and support Self-Service over Golden Semantic Models (Dataset).

To look at them in more detail, I need to make a division that will help categorize them a little better:

Visual templates = Templates focusing on the visual side of the output. They do not contain a description of how the data is to be handled.
Data templates = Templates focused only on working with data, how to connect to a specific data source, and what transformations must be performed.
Combined = Templates containing elements from both mentioned above.

Wait, there are so many templates in Power BI that it needs to be split like this? Yes! So, let’s start from the beginning.

Visual templates

As mentioned, these templates carry specific reusable settings for the visual layer. But they don’t carry data. In such a case, developers/users usually come across “.json” files with Theme definitions for reports.

Theme templates

It is a “.json” file you can export from any Power BI file. For some time now, Power BI Desktop has also included a relatively practical editor that enables basic editing of this template.

Theme template editor in Power BI Desktop

But as the Microsoft repository on GitHub reveals, much more can be defined within this template than just the basic palette of colors, backgrounds, and fonts. It is possible to add your own icon sets for conditional formatting and make specific settings for individual types of visuals so each can be completely ready for its purpose from the start.

Luckily, we don’t have to learn this “.json” fully because community editors created that can make your work very easy. Each of them has a slightly different ability and price tag (some are free, of course)

These templates are helpful; using them to make your work easier and not having to set up each visual / color separately is a good idea.

Online templates

Do you need help finding this option? There are also templates within Power BI Service / Microsoft Fabric. They are always connected to precisely one semantic model, which means that if you want to use them, you need to create/connect them to individual semantic models where they can be used. Imagine an average Power BI Report in the Power BI Service, offered as a template if you are trying to create a new report from an existing semantic model.

Online template in Semantic model details

Do you need help finding this option for yourself? This is entirely normal. You need to set up a report as a template to see this option. It’s complicated? Not at all! You select the report that the template should behave in and then add the suffix “(Template)” to its existing name. So, the resulting name will look something like this:

Name: HR Reporting (Template)

Name of templated report

The option above is unblocked as soon as Power BI Service / Microsoft Fabric registers the presence of this suffix in a report connected to this semantic model. This online report template behaves similarly to the “.pbit” template without carrying information about transformations because a Thin Report is created when used. BEWARE of what kind of information we show in these reports because they will constantly be recreated, so we should leave only the necessary elements in them, or preset visuals, in which you can change the data and work straight away.

So now we know how to create them. But should I use them? Yes, but only sometimes. Although this feature is interesting, its full use depends on your concept and direction of Power BI in the company. They can help you the most if you want to provide online users with the opportunity to create standardized reports based on pre-made semantic models. At such a moment, however, it is also a good idea to mark these models in such a way that it is possible to identify this possibility without the need to open every single semantic model and find out whether it can train users that they can expect something like this in specific scenarios and work with it.

Data templates

In these templates, I include those that manipulate data in some way or allow this manipulation and can be reused for the same or a similar purpose. At the same time, I do not mention “.bim” files here, which could be taken similarly after certain modifications.

PBIDS Files

However, these files are only encountered by a few users or developers. At the same time, these are convenient files that Power BI developers can use to facilitate the initial work when connecting data from sources.

Inner structure of PBIDS file

The PBIDS file contains information about which “connector” should be used for the data within the JSON notation, and you can directly specify, for example, the database server + a specific database. So, the Developer only needs to log in.

Developer log-in in PBIDS

Connecting to data is elementary knowledge. Do we need template files for this? Yes, but also no.Of course, there are cases where this may not need to be needed if I’m connecting to a source whose connector is named the same as it. On the other hand, the relief of not remembering the name of the database server, let alone the name of the database from which I have to take the data as a result, is quite pleasant. I would put it in a slightly different analogy: how many of us remember the address of the virtual machine we connect to? How many of us were very happy that we didn’t even have to enter it in the beginning (before the app finally saved it) because we received a file from which the import was made into the appropriate RDP application?

If I add that applications/systems could generate these files immediately, and thus you don’t even have to think about what data layer was used, I no longer need to promote this type of template.

Do you know that, for example, these files can be generated within Azure SQL Database? Your Azure SQL Database -> Power Platform -> Power BI –> Get started

Azure SQL - Visualize in Power BI

Great. How about instructing your in-house system developers to prepare such generators for you? It’s not complicated! All about them can be found here: Power Query Templates In Excel And Fabric

Model.json

What is it? If you’ve ever created Dataflows Gen 1, you’ve probably noticed the option to export as JSON. If you click on it, you get a “.json” file that starts with the name of your dataflow, and after opening it, you find out that, in addition to the M code, it also contains a lot of names, labels, and settings that apply to the given dataflow.

Okay, okay, but why did I name it “model.json”? This is again a slightly more branched answer: “Each Dataflows Gen 1 stores two types of files - CSV + JSON - in the subordinate Storage Account gen 2. These types maintain the produced data (CSV) in snapshots and metadata (JSON), as you get for calling export to a JSON file. Snapshots are also maintained for these metadata, but the main one, or the currently used one, is named modej.json, so that’s why.”

If Dataflows Gen 1 stores data in your Storage Account and you can access these files, you can make changes in the dataflow without having to make them through the Power Query Online environment. However, snapshots of “model.json” are not created at that moment; be careful! You can damage the entire data, and support will not be able to help you. So it’s at your own risk.

And why am I ranking this as a template? That’s easy! These files can subsequently be used to create a new Dataflow Gen 1. Select the import from json option in the first window.

Import from JSON

Don’t worry; the system is smart enough not to overwrite existing dataflows, but it will start creating a number series with the given name.

Power Query Templates

The last of these templates is a Power Query template. This is something we know, for example, from Excel or Dataflows Gen 2. A particular file can be exported, carrying the M code to be applied. So far, this type of template can only be used in some places, like in Dataverse Dataflows.

Initially, I had planned to write a bit more about this type of template, but recently, there was a great article that describes them, so here is the link: Power Query Templates In Excel And Fabric by Chris Webb or here you can find article from Nikola Ilic What is a Power Query Template and why it’s a big deal in the era of Fabric?.

Combination templates

So now we are finally getting to the level of not only dealing with the visual or data level. These templates allow you to prepare both. Of course, I will mention the variants only for users with access to Power BI Desktop.

Power BI Template Files (PBIT) + Power BI Projects

This file type contains the following:

Report pages, visuals, and other visual elements.
The data model definition includes the schema, relationships, measures, and other model definition items.
All query definitions include queries, parameters, and other query elements.

This allows us to prepare complete reports along with connections to source systems and provide this file to users so they can get ready answers exceptionally quickly.

PBIT: When opening them, the user must fill in the individual mandatory parameters and log in to the data sources. Once this is done, the data will be obtained and transformed, and the report will be rendered. It is purely a template file type.

PBIP: Power BI Project is the latest addition to the family of Power BI files, which stores individual background files in a readable and versionable form. This means that the use is not only for templates but for any development that should be versionable.

The advantage of these files is that you can prepare everything necessary and save the result without data. But this may only be desirable in some scenarios… in such moments, we can reach for the PBIX files themselves, which can serve us just as well.

Community solution

The community works with various of these templates regularly, resulting in many solutions you can use to create customizations of these templates. I have chosen some that are the most practical. (I certainly don’t know all that exists.)

Copy Activity, Dataflows Gen2, and Notebooks vs. SharePoint Lists

Mon, 30 Oct 2023 10:00:00 +0100

In a time of Fabric, it’s worth pointing out our three options for data ingestion.

Data Pipelines with Copy Activity
Dataflows Gen 2
Notebooks

We must compare them to understand what each can offer us from different perspectives. To be able to compare them thoroughly, there are some guardrails that we need to set so that everything goes the same way.

Guardrails and assumptions

We have three Sharepoint lists. Each of them contains different data and different columns.

Employees
- 50 rows
- 10 inner columns
Products
- 350 rows
- 5 inner columns + 1 lookup column
Sales
- 12000 rows
- 7 inner columns

We need to get the data from these lists to our Lakehouse. But data needs to look like exactly as it looks in Sharepoint! We don’t just want IDs; we want values. Each method will store data in its own Lakehouse within its workspace. All of them will be measured in a Fabric Capacity Metric perspective to compare Duration(s) and Capacity Units (CU) usage. At the same time, we are interested in the laboriousness of such data migration and the ability to fulfill the assignment.

So we have a basis. It’s time to look at the first option to get this data.

Copy Activity

It is an activity within Fabric Pipelines that allows us to take data from one place and transfer it to another. It also allows us to change the data storage format.

The activity is straightforward, and basically, everything here is guided by the UI, which differs depending on whether we want to use the activity as such or get help from Copy Assistant.

Copy data

Since it will import data from several lists, I will use help from the Assistant, who will create a parametrized process at the end.

Initially, you have to choose your data source and continue by completing the prepared steps. Does it seem too simple? It is well! Someone put a lot of work into this component to simplify the integration process.

Anyway, yes, even Sharepoint Lists are here among the connectors.

Sharepoint Lists Connector in Assistant

Either way, you need to log in to the data source. For Sharepoint Lists, we only have the option to log in using Service Principals. We already know the same option from DataFactory, where only this option exists for logging in.

Service Principal Authentication

Immediately after logging in, we can select the individual sheets we want to obtain data from. Everything is augmented with a data view, which is critical for us because Lookup columns and even Choice columns have data stored slightly differently, and we need to see if we’re getting the data we need.

Data Preview

When checking the data from the preview, I found that the data from the Choice columns has been returned correctly, but unfortunately, not from the Lookup columns! For them, only their ID is returned in the column with the pattern <column_name>& “Id”. We can solve it by expanding the query through system queries using $select and $expand.

As part of this activity, there is support for custom queries that we can execute against the Sharepoint List. Like $select, which works great!

Custom query - $select

Unfortunately, I discovered that neither Data Factory nor Copy Activity within Fabric supports the $expand parameter. No error is returned when it is validly used, but an empty column occurs.

Custom query - $select

Because I can’t get all the data for the first time, this option ends here for me. Of course, it is also possible to bring linked tables and join them at the Lakehouse level, but only sometimes. For example, we would not get the internal tables Author, Owner, Creator this way. But to have at least some comparative results regarding capacity usage, I finished loading the data by loading at least IDs in the problem columns into Lakehouse.

Costs of Copy Activity

The measured capacity is excellent. Duration but also CU is relatively low. We’ll see how the rest of the variants turn out.

It’s time for Dataflows….

Dataflows Gen2

Generation one and two differ in various aspects, which we could talk about, but for us, the only option is to use generation two because it can store data in Lakehouse.

Compared to Copy Activity, Dataflow provides many more capabilities, mainly towards data transformation. We would probably get better results if we included the option to write our M code from scratch. But a regular user won’t do that. For that reason, I’m going to stick with no code for this time, follow the example of an average user, and only follow the path I can create with the UI.

The connector for Sharepoint Online sheets is here so that we can use it immediately.

Dataflow connectors

When setting up this connector, we again fill out a simple window asking us for the address, implementation of the connector, and authentication. As part of the implementation, we have two options - 1.0 (null) and 2.0, when each approaches data acquisition in a slightly different way ~ SharePoint.Tables - PowerQuery M. The consensus is that the 2.0 implementation is faster, so I’ll go with that variant. Most users will also say that newer means better, although this may only sometimes be true.

Connector implementations

Unlike Copy Activity, Dataflows Gen 2 supports authentication via an organizational account, not just the Service Principal (SP). This additional authentication option can be convenient for many developers who don’t have access to SP.

Dataflows Authentication Options

After connecting to the data, there is a classic time for transformations, setting data types, and expanding associated fields that contain values from associated tables due to Lookup columns.

The big difference in Dataflows gen 1 and 2 is the staging option, which creates child Lakehouses for its purposes… Based on an article from Chris Webb - Fabric Dataflows Gen2: To stage or not to stage?, I have disabled staging to make it more performable.

Staging of Queries

With dataflow, it is often necessary to measure, in addition to the performance of this artifact, the performance of the associated ones. Fortunately, we don’t have to deal with child Stage Lakehouses when staging is turned off. However, since I’m only interested in the workload of the given artifact at the moment, I only looked at its direct effects on capacity. It immediately follows from these that they are higher than in the Copy Activity and very significantly!

Costs of Dataflows

So there are two options. But what about the third one?

Notebooks

This is where we end with UI. Notebooks are code-oriented unless I consider the Data Wrangler option.

Therefore, the performance will depend on what we want to solve in the code. Anyway, here we have to solve authorization, pagination (because Sharepoint returns data after a maximum of 5000 records), and other aspects.

I will not discuss here how to do this specifically; you can find out in the article by Adam Hrouda - Import SharePoint data to Fabric, so don’t hesitate to read it.

For context, the data would not be returned directly here either, so the query needs to be expanded to include the previously mentioned $expand and $select.

Costs of Dataflows

For completeness, I mention here what the defined scheme looks like, which is necessary if I want to use it in this way, also with joined tables.

Defined Schema

Costs of Notebooks

The used nootebook solves a lot of things, and even so, its performance is very good. It is significantly better than Dataflows but, at the same time, worse than the first variant via Copy Activity.

Comparison

Which of these options should I use? IT DEPENDS!

If we need to get data from one sheet that contains only its data and not data from associated sheets, then the most straightforward answer is - Copy Activity. If we also need data from the associated sheets, then Notebook, at least for now, until we get support for $expand in Copy Activity. When we don’t have access or the ability to get Service Principal for Sharepoint, then Dataflow is the answer!

Criteria	Copy Activity	Dataflows gen2	Notebooks
Fulfilled requests	NO	YES	YES
Duration(s)	180	3 151	981
CU(s)	2 160	51 127	3 926
Lookup Value Support	NOT NOW	YES	YES
Require coding skills	NO	NO*	YES

* But coding it can help you to make it faster.

Personal Workspaces in Times of Fabric

Tue, 15 Aug 2023 11:00:00 +0200

This area is quite often forgotten in Power BI and, thus, in the new Microsoft Fabric. At the same time, these are spaces that, for a long time, eluded the access of admins and acted more like a black box or storage area. But many things have changed over time, and at the same time, there are options and information that should be voiced more. This is a shame because that makes these spaces seem mysterious, multitudinous, and dangerous.

Are the concerns justified? To be able to answer this, personal workspaces need to be analyzed and understood from different angles.

Personal Workspaces and Their Purpose

To begin with, I borrowed an excerpt from the official Microsoft Learn documentation: “My workspace is the personal workspace for any Power BI customer to work with your content. Only you have access to your My Workspace. You can share dashboards and reports from your My Workspace. If you want to collaborate on dashboards and reports or create an app, you want to work in a workspace.” Service Basic Concepts

This description shows that the content you put in “My workspace” is only yours, and no one else can directly access it. This is precisely why, in my experience, many larger companies or companies with high regulatory requirements fear them. They usually comment with something similar: “Users can publish anything there. We can’t control it; not all our data is allowed in the Cloud, even in Microsoft Cloud. So how can we turn off the usability of Personal Workspaces?” ~ You can’t! Unfortunately, this is a sad but understandable reality of concerns I often encounter. They usually add another postscript to it, saying that people publish something there, and then they can still share it with someone with the appropriate license. They are right again. It works. Content can be shared with someone else from the personal workspace. In this case, the primary limitation is that the given user does not get to the workspace directly but “only” gets to specific content.

This sounds like a significant disadvantage of the whole concept of personal workspaces. But before discussing what has already changed, we must say something about their positive effects.

My workspace is a space for Personal BI. Because Power BI is very often taken as a Self-service BI tool and has been directed to it for a very long time. It is necessary to have some space where I can store my analysis and research and possibly consume them without the necessity of an approving process for publishing content to some shared workspace via a third-party application or waiting for a new workspace to be created so that the content I create for myself doesn’t overlap with someone else’s content and we accidentally overwrite each other. It may seem that it is not enough, but the reality is that this contribution is enormous and significant. At the same time, it can serve you to test design, online behavior, calculations, speed, …, but with NON-SENSITIVE data! So, If the company is trying to achieve Personal BI, then it sounds great.

Types of Personal Workspaces

For Admins, however, it is necessary to understand that there are two types of these spaces: “PersonalGroup, and Personal.” You can find this split if you start pulling the REST API (for example, using Groups GetGroupsAsAdmin). For your understanding, I am attaching the resolution that I deduced based on the returned values from the API:

PersonalGroup = Classic “My workspace,” which is created for each user as well as the Application (Service Principal) that enters the Power BI / Microsoft Fabric environment
Personal = A space created by an external application using some “black magic” aka system-generated. A typical example of this type of workspace is the workspace that is created for each SharePoint sheet in which you create a Power BI report via Sharepoint - Power BI integration (from a Sharepoint sheet)

They look different but behave very similarly. There is an owner under which the given workspace lies, and as long as the given owner exists in some way, this space also exists. If I delete a user who had a PersonalGroup, this space should move to the Deleted state, and over time its name will change to the original user’s GUID, and over time it will be deleted entirely. With Personal, the test took quite a long time for me to confirm some behavior, but after a specific time, when you delete, for example, that Sharepoint sheet, this workspace will also be deleted. These two behaviors reassure all Power BI / Fabric Admins that the content they produce will be recovered if the user leaves.

On the other hand, is there any way to get to it? After all, is that workspace “only” for that user? (will be answered later)

Note: Looking at the overview of workspaces in the Admin portal, both types are combined under one type. Which can be very confusing when comparing UI and API responses.

There is, however, one very often neglected difference and, at the same time, an important point. PersonalGroup is the primary repository of specific messages generated for a given user. Such reports include, for example, Teams Activity Analytics, you can find more details about this report in the documentation ~Analyze Teams usage in Power BI for Teams~. This should also be considered because a new report from the Microsoft workshop will sometimes appear, which will also be saved here for users.

Self-Service BI and Personal Workspaces

However, Self-Service BI in a company will take time to function correctly; thinking about many processes, requirements, data plans, data ownership, and the unification of truths is necessary. After all, no one wants two controllers to meet at their table, and both of them have different results of the investigated issue because one calculated it with one method, the other with another, and drew data from Excel, which they have kept on their computer for a quarter of a century. Maybe you think it’s funny, but unfortunately, it’s a sad reality that happens very often.

At the same time, it must be understood that the path to Self-Service has certain stages according to the maturity of your company in different areas. This is covered in the official documentation under Power BI adoption roadmap maturity levels - Power BI.

Remember how I said I often hear requests to turn off personal workspaces? So I will add that it is most often for three main reasons:

The main reasons

The lack of trust in users stems from the earlier stage of the company’s maturity. Users must learn the technology and how to behave if it’s just starting with Power BI / Microsoft Fabric. Do you think that after four years, everyone in a company with, for example, 200 people already knows what they can afford? It depends! On what? On the quality of the adoption process, the education that has been provided to all users, and the checking of compliance with the internal regulations related to it. Sometimes it can happen. I will publish to a different workspace than I originally wanted. It shouldn’t happen, but it can. So it is good to have a process in the company to correct such a mistake. I’m not talking about “just” deleting content. I am also talking about identifying the cause. Distraction is one thing, but very similar workspace names without much distinction are another.

If we invest enough effort in high-quality user training and the layout of workspaces (including their labels and names), we can significantly reduce the risk of the problem occurring. This is one example that also applies to the publication, there are more options for improving the work process with this tool, and at the same time, it is always a little different from company to company according to requirements. It always boils down to three points… thorough analysis, process design, and user training!

Be a guide to your users

If I dwell on data security for a moment, I have also encountered several times that there was a requirement to use one’s key for data encryption. A whole chapter of the documentation about this topic is Bring your own encryption keys for Power BI - Power BI. If you read this part of the documentation carefully, you will find that encryption with our key applies to data stored appropriately. However, Personal Workspaces natively lie on classic shared capacities like PRO-type workspaces. So will they also be subject to encryption of some other capacity? If they stay in this native form, then NO! BUT… if we would change these personal workspaces’ underlying capacity to the capacity encrypted with our key, then YES!!! (How to do that is hidden in the next chapter)

Possibilities and Settings of Personal Workspaces

Personal workspace settings are slightly different from standard workspace settings. This type of space does not support most of the options, except changing the associated capacity, an overview of used storage, embed codes, and then support for Spark compute and Library management settings.

You’re currently dealing with the capabilities of Microsoft Fabric, so you’ve definitely gotten smarter, mainly because of the last two mentioned. After all, “my workspace” does not support the production of Fabric components. Or does it? YES, it supports! But there is one catch. Actually, two,… maybe even three.

To create Fabric components here, this workspace, like any other, needs to be supported with Fabric capacity. I welcome this possibility as part of the Fabric Trial so that I don’t have to “clutter up” some shared space with my tests, but what if this trial doesn’t exist? A significant advantage of standard workspaces is, for example, the new Git support or the possibility to pin the given workspace to Deployment pipelines and transfer some of the content elsewhere. However, both options need to be included in personal workspaces. Add to that the concerns and issues mentioned at the top of the article, and you have a fantastic package that will cause no admin to give you capacity for your personal workspace. ~ Please put this thought out of your head, and let’s think about the possible use of this potential without the concerns mentioned earlier.

We know we have been promised two classic financing options for Microsoft Fabric → Pay-As-You-Go and Reserved. We already have Pay-As-You-Go today. Even if we do not receive the reserved model in the future, this consideration remains valid: Within the company, we use F64 or a variant that already includes Power BI Premium and primarily the ability to read the content of Power BI components for Free users. As part of this capacity, we run various updates, queries for data in OneLake, etc… We probably don’t want the developer to develop for us at production capacity and thus drain from it the resources necessary for the smooth operation of other artifacts. We will therefore create some additional capacity with a high probability that the capacity will be weaker than the original one. Our existing developers are doing a great job on the new capacity, and Deployment Pipelines are deploying everything to the workspace in production capacity.

But where should we send a new/junior developer to learn how to use Fabric artifacts and complete onboarding training, where he will not take away computing capacity from other developers? Why not use the lowest option of F2 capacity, which would be turned on only for a limited period, and pin this capacity to the personal workspaces of these beginning developers? Most onboarding training does not result in a functional production solution but are demo materials that users should have if something needs to be repeated or relearned. At that moment, using “my workspace” seems rational and legitimate.

Experimenting is part of learnin process

To give you another piece of the puzzle, I’ll add that the average user can’t just pin the capacity under their “my workspace” from the workspace settings (although it seems like they probably could) to be able to do that, so would have to have special authorization for this within the capacity. And pinning capacity to someone else’s space is done from the Workspaces overview within the Power BI Admin Portal / Fabric Admin Portal. The workspace then has the same diamond mark as a normal one.

My workspace

It is also possible to set all personal workspaces to be automatically created with a specific capacity. This ability to pin capacity did not come with Microsoft Fabric but has long been a part of Power BI. You can even pin the capacity of the Premium Per User license. What’s new is that Admin can prevent you from switching your workspace back to shared capacity.

Block user from reassigning personal workspaces

But back to the fact that personal workspaces can be assigned below capacity. This is something that many users and even admins don’t know. Let alone the fact that it is possible to pin them there automatically. So it will be good to know how to do it!

Assigning a Personal Workspace to a Capacity

There are two main ways to do this. For the first one, I already described that it could be done directly in the workspace settings. Still, as a reminder, I emphasize again that you need to have the appropriate license within the given capacity to do it! Therefore, you must be a Capacity Admin or Capacity Contributor.

Personal Workspace settings

I have yet to consider the second option. As part of that, I can add any personal workspace, not just mine, under some capacity in my administration (so again, it requires permissions, as mentioned earlier). Within the Admin Portal, select the Workspaces tab.

Workspace blade in Admin portal

Here I need to find the personnel workspace I’m looking for and use the icon of three vertical dots to open the submenu and select Reassign workspace.

This path offers one option you need in the first option, even if you are a Power BI Admin. I am talking about adding capacity from Premium Per User under “My workspace.”

Reassign workspace

I dare to add a third option! And the automatic one, when all “My workspace” is automatically pinned under one selected capacity. Attention!!! You do not have this option in the TRIAL variants. Of course, this option is also missing with Premium Per User settings. Let me split this option into two parts New, Existing. For new personal workspaces, the best option is to turn on the following option in the selected capacity settings.

Capacity of Workspace

But if I want to pin an existing one, don’t worry. There’s no need for a ticket towards MSFT support, manual clicking for every workspace, or PowerShell magic. It is enough to add workspaces to the capacity by selecting the “Assign workspaces” button, which will show you additional options.

Preferred capacity for all new workspaces

Among these, you can find an option for mass transfer which is ideal! Assign workspaces

If you have more capacity, you can perform mass migration of workspaces with this option.

Assign all personal workspaces to capacity

Boosted capabilities of personal workspaces with capacity

I mentioned earlier that adding capacity to the personal workspace can give users their sandbox. From a capacity point of view, this should be separated from production and development so that it is indeed a sandbox.

But if you support the personal workspace with capacity, you add many options you can use. Of course, it depends on the type of capacity, but if we were to support it with, for example, Fabric capacity, we can use all these artifacts:

Personal Workspace Fabric Cheatsheet

In addition to these artifacts, something else is hidden here that is very valuable and necessary. After all, I cannot simply transfer my content from my workspace to another. This should help me because it is a personal analytics environment or a sandbox. But what if there was an option to connect this space to Git? Then one could also think that I can send the change from “My workspace” to another so that the variability of these workspaces would increase again.

Personal Workspace with GIT Integration

I have good and bad news for you. The workspace modified in this way can be connected to Git, you can even commit, but there is a catch. These workspaces, or their UI, have quite a bit of trouble with this option and often disappear like the rest of the settings, where it even lets you change the name. Don’t worry. It won’t let you!

Error message when trying to overwrite the name of workspace

Here is a trick that will allow you to display these particular options. Create an artifact from Fabric, return to My workspace, and suddenly you see new possibilities again. After the next refresh of the page, those options will disappear again. So I was setting up new Notebooks so I could test it. At the same time, I noticed that possibilities remain forever and do not disappear—for example, the Lineage view.

Lakehouse in Workspace

There is still a sad minus. Streaming Dataflow, an artifact slowly leaving the Power BI Service, is still offered here in My Workspace when creating a new artifact. If you try to start it, it will return this message.

Unable to create artefact

Even with streaming dataflows ending, it will be good to remind all users of this message so they don’t forget it (if they use them). Everything seems to be caught so that even if it offers that you can do something you shouldn’t, it immediately informs you that you can’t do it. Even though I would have preferred a proactive solution rather than a reactive one, which at this moment I would instead irritate the user, I would have preferred a preventive solution, where I would not be offered anything that I really can’t do or at least these options would be greyed out (deactivated).

Auditing, Accessing, and restoring personal workspaces

Now the exciting part completely refutes some of the concerns from the beginning of the article. Over time, the possibility was added for the Admin to assign access to the personal workspaces of any user, which was an option that all admins very much welcomed because, at that moment, you break down the ideas of a black box over which you have no management options except for assigning capacity, previewing names and types of stored content. Now, if something doesn’t seem right to the Admin or he wants to check or help with moving content / solving a problem, he allows access for 24 hours. So for 24 hours, the Admin will see the given workspace among his workspaces, and the name of this workspace will be the name of the given user. According to the documentation, it should have the “my workspace” icon, but when I write this article, the workspace assigned in this way has the same icon as shared workspaces. (Admin can’t assign access to anyone but myself!)

Standard Workspace logo

If we delete the user from AAD (respectively from Entra), according to what we said, his workspace will also be deleted gradually. But before the workspace completely disappears, it can be restored from the Admin’s point of view as a standard application workspace so that more users can be added to it, and thus, the content does not disappear and is not trapped here! You can find an exact description of how to do it here ~ View workspaces.

As far as auditing is concerned, I can access the content and its details using the REST API, just like with any other workspace. If I were to ask for ”./myorg/groups”, for example, I would not see personal workspaces, not even my own. I have to request Admin rights ”./myorg/admin/groups?$top=xxx” and get it. Of course, it is also possible to ask for help from the ScannerAPI (Metadata scanning - Power BI) to scan them and return deeper details. However, if you need more than these variants and want to know what the user is doing in this workspace, then you have two main options! Use the new Admin monitoring, where you can find the appropriate personal workspace in the Feature Usage and Adoption report on the Analysis page to look at the operations performed. However, this report is in Preview so it may be unstable. For example, I noticed that it currently only works with some of the operations that the user can do. If I wanted to get all of them, I could call the API again using the Activity Events endpoint. This endpoint returns the same data as the O365 Compliance Center. The bases for working with this endpoint can be found in this link to the official documentation Track user activities in Power BI or here in more specifics Get Activity Events. Also in documentation you can find this great part about implementation and planning Tenant-level auditing

All these auditing and heap control capabilities, including support for private key encryption, should hopefully give you peace of mind about your workspaces, as they allow you to identify the content that users are storing there, how they’re working with it, and of course, reacting. It requires knowing that it’s good to watch these types of workspaces. To remind you, what kind? Personal Group! These are the workspaces you’re looking for, and you can’t disable them.

Auditing is necessary for you and helps you keep everything under control

Summary

Worry usually stems from what we don’t understand and can’t control. Therefore, concerns also arise from personal workspaces. On the other hand, we already know this is not a black box of mysteries to which no one but one person has access, and we may not lose the contents.

Many thanks to the wonderful Melissa Coates for her help and review of that article.

I have a little summary for you at the end:

But do they require our increased attention? If we’re Power BI admins, sure we do. But! All users should be properly taught how to use them also.
Are they a danger to us? Only if we don’t audit them and if we don’t educate our users.
Can they serve us for testing or educational purposes? Definitely!
Can the data inside these spaces be encrypted like in regular workspaces? Yes, they will, but it requires pinning them under the encrypted premium capacity.
Can a deleted personal workspace be restored? At some point, yes, and that’s why they need to be monitored.
Can we ban them? NO!

Lessons learnt from PySpark Notebooks and extracting APIs

Fri, 30 Jun 2023 09:30:00 +0200

APIs are great source of data and are provided by many systems. It is mostly a REST API that is based on the HTTP protocol. Already in the article APIs as Power BI Datasources, I explained in a simplified way how APIs work and what needs to be passed to them. But it was through the lens of Power Query. Some time has passed since then, and Microsoft Fabric was added to our data world, which in its interface, allows us to use not only the already mentioned Power Query but also PySpark through Notebooks.

I’m not a Python or PySpark expert, but I’ve been playing and learning with it for a while now. So I would like to share with you what I have noticed so far, what has paid off for me for the procedures, and what to watch out for. If you are a Python expert, I apologize for my possible mistakes. I will be happy if you write them in the comments so that I can correct them and learn more myself. Thank you so much for your attention and participation.

At the same time, if you have already worked with notebooks, you can skip the next chapter. If not, I recommend you read it, because in it you will learn basic information about how to work with notebooks.

Introduction to Fabric PySpark notebooks

But to be able to jump into it, we have to create a notebook of some sort. You can create a notebook within the Data Engineering and Data Science views in Fabric inside a workspace with a Fabric Capacity.

Fabric notebook

The notebook is created immediately after selecting the button with its icon, so it is good to have at least a basic overview of what I might be interested in in the notebook:

First opening of notebook

Of course, you can also see from the picture that there is an integration with Visual Studio Code, the possibility to connect LakeHouse, in which we want to save data or read it in reverse. If you have never worked in a similar environment and have no experience with Jupyter Notebooks, for example, then just brief info. A notebook is an environment in which you can write code that executes the language set in it. It is mostly PySpark, but we can also use Spark Scala, SparkR, or Spark SQL here. You can write code in individual cells that are executed sequentially. We then decide for ourselves which cell to start and which not to. We can also use the so-called “Run all”, which will start all the unfrozen cells in their exact order. The results of individual cells are then displayed below them. The results can be textu, table, chart, graphics, or even HTML.

Cell result preview

We have two types of cells. Markdown cells and Code cells. What can be seen in the previous image is a code cell because it supports the execution of code in our chosen language. Markdown cells are cells that support writing text in Markdown syntax. Markdown is a simplified HTML notation that allows us to write and format text simultaneously. Many people use this notation style for its uniformity and simplicity. Even this website is written in Markdown, and only afterward is it converted to HTML.

Markdown cell

As you guessed correctly, Markdown should be used to describe what is happening in the cells in the notebook. At the same time, it can be used precisely with the help of headlines to divide your code cells into logical units.

Logical parts

It is then possible to navigate the notebook much better, and at the same time, individual parts of the notebook can be started independently.

Like most other Fabric components, the Notebook has auto-save, so your changes are regularly saved. That’s why I recommend using new cells for tests, which you merge or delete but do not modify cells you have already created and are functional.

My last practical tip of this type is that on the View tab, you can find a list of all the shortcuts that will help you work on your notebook. Shortcuts will show up depending on your operating system, and there are quite a few of them, so I highly recommend checking them out.

Shortcuts

But we already have the notebook, so back to the API topic.

How to get data from API

To query the API, we will need some basic libraries that will allow us to do this. When I searched for such libraries, I found more of them, but in the end, the library that suited me best: requests, which is very easy to use and at the same time supports all necessary operations.

But it needs to be added to the Notebook to use. We do this using the following code:

import requests

Place these two magic words in the cell set as PySpark (Python) and let go. The library will be installed on the environment in this particular notebook, and we can start using it. It’s a good idea to make one cell where we’ll do all the library imports we’ll need. To avoid an error the next time you start the Notebook, because the library will not be installed, it is good that this cell with installations is always located at the beginning of the Notebook.

Other important imports we will need are:

from pyspark.sql.functions import *
from pyspark.sql.types import *

These allow you to use the functions and types PySpak offers. The asterisk at the end after import tells us that we want to import everything from the library. If we wanted only some functions, we would have to list them explicitly.

But back to requests. The request library gives us the option to either explicitly requests GET, POST, etc. calls against the API or use its “requests.request()” function, where we enter the type of request as the first parameter (just mentioned GET, POST, etc.) and as the second parameter we enter the URL we want to query. We can then save the result in a variable and continue working with it.

response = requests.request("GET", "https://api.openweathermap.org/data/2.5/weather?q=Prague&appid=<YOUR_API_KEY>")

I liked this “.request” variant because, for example, when downloading data from the Power BI REST API or interacting with this API, I can create my function, which I pass the call method and the RelativePath that I need to call. It returns results without having to define HTTP Headers and Base URLs repeatedly. (because they will always be the same)

For example, such a function might look like this:

def get_api_data(method,relativePath):
  response = None
  headers = {
      'X-Api-Key': "xxxxxx",
      'content-type': "application/json"
  }
  callUrl = url + '/' + relativePath
  response = requests.request(method,callUrl, headers=headers)
  return response

The function is called “get_api_data” and has two parameters: method and relative path. The Headers declaration is defined in it, where you only need to add the X-Api-Key. At the same time, I started defining the Base URL, which in this case is stored in the “URL” variable. This variable is defined at the beginning of the Notebook and contains the base part of the address needed for all calls against the API. Similarly, we can define here any header options that we will need. The entire address is then assembled using a simple concatenation, which is one option. If we need to place the text in a string, it is possible to use the so-called f-string (string interpolation), which looks like this:

myVariable = "very"
f'This can be {myVariable} helpful'

But it is necessary to say that you should never insert passwords, secrets, keys, and generally any important information into plain text variables! This information should be stored in Azure Key Vault, which we should query for it. If you would like to use Azure Key Vault, for example, to implement OAuth2 via Service Principal, you can use the following template:

# Import of the Trident Token Library
import json
from trident_token_library_wrapper import PyTridentTokenLibrary

# Parameters
url = "https://api.fabric.microsoft.com/v1"

# Name of the Key Vault
nameOfKeyVault = '<NAME-OF-KEY-VAULE>'
keyvault = f'https://{nameOfKeyVault}.vault.azure.net/'

# Names of the secrets saved in Key Vault
tenantId_SecretName = '<NAME-OF-TENANT-ID>' # Tenant ID
clientId_SecretName = '<NAME-OF-CLIENT-ID>'    # Client ID of Service Principal
clientSecret_SecretName = '<NAME-OF-CLIENT-SECRET>' # Client Secret of Service Principal

# Function definition
## Function for getting secret from Key Vault
def get_secret_from_keyvault(secret_name):
    access_token=PyTridentTokenLibrary.get_access_token("keyvault") # Get Access Token for Key Vault
    return PyTridentTokenLibrary.get_secret_with_token(keyvault,secret_name,access_token) # Return Secret

## Function for getting Bearer Token
def get_bearer_token():
    resource = "https://analysis.windows.net/powerbi/api" # Resource for Power BI API
    tenant_id = get_secret_from_keyvault(tenantId_SecretName) # Get Tenant ID
    client_id = get_secret_from_keyvault(clientId_SecretName) # Get Client ID
    client_secret = get_secret_from_keyvault(clientSecret_SecretName) # Get Client Secret
    url = f'https://login.microsoftonline.com/{tenant_id}/oauth2/token' # URL for OAuth2
    data = f'grant_type=client_credentials&client_id={client_id}&client_secret={client_secret}&resource={resource}'  # Content that will be sent for OAuth2 
    headers = {'Content-Type': 'application/x-www-form-urlencoded'} # Headers for OAuth2
    response = requests.post(url, headers=headers, data=data)
    return response.json()["access_token"] # Return Bearer Token

## Function for getting response from API
def get_response_json(relativePath, method, data, payload_object):
    fullurl = url + '/' + relativePath # Full URL for API call
    bearer_token = get_bearer_token() # Get Bearer Token
    headers = {'Content-Type': 'application/json', 'Authorization': f'Bearer {bearer_token}'} # Headers for API call
    response = requests.request(method, fullurl, headers=headers, data=data)
    if payload_object == "": # If payload_object parameter is empty, return response as a JSON
        return [response.text] # Return response as a text
    else:
        return response.json()[payload_object] # Return response as a JSON and select only the payload object

Thanks to this, I can make, for example, the newly added call to get tenant settings via Tenant Settings of the Fabric REST API endpoint:

source = get_response_json('admin/tenantsettings',"GET", "", "tenantSettings")
source

Tenant Settings Call

Saving data to LakeHouse

As I already mentioned, we have two basic options. Either we save our data as files or as tables. If we decide on files, we can choose between different types of files, including JSON, CSV, or even Parquet.However we can not save the file directly within Pyspark. It is necessary to convert it to a DataFrame (abbreviation df) and then save it. .

There are many variants of how we can get a DataFrame, and not all of them always work because some are limited, for example, so all levels/columns in the file always contain the same number and the same names. This can be different, especially with APIs! You will often encounter that the API only returns some of the name fields if any are empty. This makes perfect sense because why would an API return name fields that contain no value. But we have to be able to solve it somehow. Fortunately, a “spark.createDataFrame()” function allows us to create a DataFrame from any source. In our case, the JSON file we got from the API.

source = get_response_json('admin/tenantsettings',"GET", "", "tenantSettings")
df = spark.createDataFrame(source)

If we want to see the DataFrame’s contents, we can use “.show()”.

Show DataFrame

Once we have a data frame, we can do anything with it. Filter data, delete columns, create new columns, expand nested lists, and more…

But as I said, we can write the DataFrame in different formats. In our case, we will choose JSON. To do this, we need to call the “.write.mode(“overwrite”).format(“json”).save(“Files/FOLDER-FOR-OUR-FILES”)” method on our DataFrame. The word Files is intentionally used within the save section, as this is a reserved location for files within LakeHouse. If you want to dive deeper, add slashes and define the whole path. For tables, we have the Tables location.

The code to create the files looks like this:

df.write.mode("overwrite").format("json").save("Files/ExtractedJsons")

Don’t panic! Since PySpark uses data distribution, a folder with several files will be created for you. Each file contains part of our data. In our case, it is seven files.

Created JSON Files

So if we want to create a table, we can do it as follows:

source = get_response_json('admin/tenantsettings',"GET", "", "tenantSettings")
df = spark.createDataFrame(source)
writeToLake = df.write.mode("overwrite").format("delta").save("Tables/TenantSettings")

The result is creating a delta table called TenantSettings, which we can use in SQL queries.

Created Table

I will mention one more tip, which has come in handy several times. If you want to create a table and manually define values in it, you can do that. There are more methods than usual, but I needed, for example, to create a table with one column named “id” and different IDs.

listOfIds = [["0000000-1111-1111-1111-4d23d76e87d7"],["0000000-1111-1111-1111-2abccb7b62fe"]]
columns = ["id"]
df = spark.createDataFrame(listOfIds, columns)

It follows from the code that you can still use the same method to create a DataFrame and change the column names directly. What use could such a thing be to me? For example, to use them to try to start scanning workspaces via the Power BI Scanner API.

post_WorkspaceInfoUrl = "https://api.powerbi.com/v1.0/myorg/admin/workspaces/getInfo?lineage=True&datasourceDetails=True&getArtifactUsers=True&datasetSchema=True&datasetExpressions=True" # POST URL for getting workspace info with parameters

bearer_token = get_bearer_token() # Get bearer token
header = {'Content-Type':'application/json', 'Authorization':f'Bearer {bearer_token}'} # Create header for POST request

df = spark.sql("SELECT * FROM Fabric_Admin_Storage.wsdemo LIMIT 1000").toPandas()['id'] # Get IDs from Delta table
df = {"workspaces": list(df)} # Create JSON with IDs

post_WorkspaceInfo = requests.post(url=post_WorkspaceInfoUrl, headers=header, json = df ) # POST request for getting workspace info
print(post_WorkspaceInfo.text) # Print response

Spark operations vs. non-Spark operations

I intentionally put this issue on the list because it took me a while to figure it out. That is before I opened the documentation and read why the operation I was trying to solve kept returning as an error.

As soon as we are in the PySpark DataFrame framework, many operations open up for us that we can do with such a data frame. One crucial thing is to add new columns / transform old columns. We have a “.withColumn()” method for this within PySpark.

df = df.withColumn("newColumnOne", lit("newColumnValue")) # Create new column with value: "newColumnValue"
df = df.withColumn("newColumnTwo", col("oldColumn")) # Create new column with value from old column
df = df.withColumn("newColumnThree", col("oldColumn") + 1) # Create new column with value from old column + 1
df = df.withColumn("newColumnFour", col("oldColumn") + col("oldColumn2")) # Create new column with value from old column + value from old column2
df = df.withColumn("newColumnFive", df.oldColumn * df.oldColumn2) # Create new column with value from old column * value from old column2

This feature is quite a sledgehammer because, with its help, you can add columns or overwrite existing ones! So, if I didn’t add a number after “newColumn” in the previous entry, the newly created column would be constantly overwritten. In the end, it would contain only the last entered variant.

That’s great, but what if we use something outside the PySpark library? For example, a function that can convert ISODATE “PT2H10M” to seconds? At that moment, we would have a problem to solve. Of course, we will start with the fact that we will need to import a library that allows working with ISODATE. This was quite a shock for me because I had long been used to Power Query being able to work with this notation, and immediate conversion to the duration data type was possible, which is not available in PySpark.

import isodate

But this library is not a PySpark library, so we cannot use it directly within PySpark. However, to use it, we have to use the UDF (User Defined Function) method, which allows us to use a function not part of PySpark. At that point, PySpark will not optimize it but it will be usable.

If we were to define it as, for example, a lambda function, we could use the following notation.

pt_duration_to_seconds = udf(lambda x : isodate.parse_duration(x).total_seconds(), FloatType())
df = df.withColumn("newColumn", pt_duration_to_seconds(col("oldColumn")))

Follow-up API requests and pagination

With queries to the API, it can very often happen that we don’t get all the data on the first request, and at the same time, if we want to get our data, we first have to make another query there and then branch out the individual queries to get what we’re looking for.

These two issues are the last pieces we should show ourselves because they are essential.

Follow-up API requests

It is very typical for APIs, and it is also the case with the Power BI REST API, for example, that if you want to get to some detail, you have to peel off the onion layers gradually. So the first query will go, for example, to a list of all the workspaces you have available. Then, thanks to their API, you get all the reports, and only then do you get a list of the individual pages in the reports.

This onion peeling is very typical and nothing to worry about. You have to get used to it and know that it is like that and how to deal with it.

A possible approach to the solution is that we gradually save between the stages, which we then use in other queries. These intermediate stages can be within a variable or a stored format, and it doesn’t matter whether they are tables or files. Whether we choose what is better for us depends on what options we have and what our requirements are. If we are explicitly only concerned with the lowest layer, then there will be variables. But if we want to be able to go back to the obtained values for higher layers of the onion sometime in the future, either for transformation or further insight, then, of course, it is better to use the formats for saving to Lakehouse.

If we go with the second option and want to save all of them among the results, we would proceed similarly, as shown in the previous chapter. We would run a query -> Store the data -> Load the data from a table using SparkSQL, for example, -> Run another query on the data. And so on.

In the case of the first variant, we have all the data in a variable and can work with it. To work with them as easily as possible, it is useful to narrow the entire data frame in a variable to only those columns we need if it’s just one. So much better! We can do this using the select method to select only the columns we want. Next, we can use the collect method to get data from the DataFrame into a variable with the data type list.

listOfIds = previousResponse.select(['id']).collect()

We can then review this sheet and gradually obtain the needed data, which we can do with a simple for loop.

response_list = []
for requestId in listOfIds:
    response = get_api_data("GET",requestId[0])
    response_list.append(response.text)

However, if you tried this function with the original write-up of the get_api_data() function, you would need help. The Notebook will tell you that this type of writing does not guarantee that everything will finish as it should. In general, PySpark wants to ensure that you give it a function that terminates under all circumstances. We need to modify our function a little more.

def get_api_data(method,relativePath):
  response = None
  headers = {
      'X-Api-Key': "XXXXXXXXX",
      'content-type': "application/json"
  }
  callUrl = url + '/' + relativePath
  try:
    response = requests.request("GET",callUrl, headers=headers)
  except Exception as e:
    return e
  if response != None and response.status_code == 200:
    return response
  return None

Now this function checks to see if we have fallen into Error, which it would eventually return. Furthermore, we got to the data thanks to the 200 status, and some data was transferred to us. If these two conditions are not met, then None is returned. This is equivalent to returning an empty string.

The important thing is that, in this case, the Notebook will finally let you link the query.

Pagination

There are many pagination variants, depending on how the API is designed. But we mostly encounter some parameter in the response that tells us that we have another page. In such a case, you must create a cycle to call the API and get data gradually.

Another variant is an iterative paging parameter that we pass to the API, for example, as part of an HTTP Query. Often this is simply the number of the page we want to get. At that point, we don’t know exactly how many times we will have to iterate, and we need to build a loop that will call the API and get data until it returns an empty response.

I recently solved one such pagination and concluded that it could be produced much more simply than in Power Query, where even there, it is done using List.Generate() is very simple.

page = 1 # Set initial page number
while True: 
    response = get_api_data("GET",'/user/<USER-ID>/time-entries?page-size=5000&page=' + str(page))
    if len(response.text) > 2: # Check if response is not empty or if it is not just "[]"
        response_list.append(response.text) # Append response to list
        page = page+1 # Increase page number
    else:
        break # Break the loop if response is empty

We convert the resulting sheet with responses from the API to a data frame, and that’s it. If this API looks familiar because of the address, it’s an API for a timesheet app called ClockIfy. But as you can see, it’s just a few lines of code and done.

Summary

I purposely titled this article Lessons Learnt because the few tips and examples you can find here are exactly the things I wish I had had in hand when I was starting with these notebooks or when I was struggling with them and he couldn’t solve some things.

In general, downloading and working with an API is very pleasant in PySpark, and once you get the hang of it, it’s also quite a fast thing from a development point of view. From a speed point of view, this matter depends on your API. However, I compared the speed of downloading data from the API in Power Query and Notebook. In that case, the measurements show that Notebook with PySpark is significantly faster in this case. For example, when downloading data from the ClockIfy API, the Notebook managed all the entries in 1 minute and 20 seconds, including writing to Lakehouse. The same process in Power Query took 4 minutes and 53 seconds, which is a significant difference. For now, however, it is necessary to consider that Fabric is in the Preview version, so some things can change, which can be changed for the better and worse for both parties. At the same time, the biggest bottleneck so far is writing to Lakehouse, which is still slow on the Power Query side in Dataflow Gen 2. But even that can change.

I also highly recommend Sandeep Pawar’s blog called Fabric Guru with content on PySpark, Notebooks, and Fabric in general.

I hope you enjoyed this article and find it helpful in your work with PySpark. Feel free to write in the comments if you have any questions. Likewise, if we have ideas to improve what I’ve shown here, feel free to write. I am delighted to learn something new and improve.

At the very end, I give one piece of advice that has paid off for me in any language I’ve learned: that it’s good to make a document/cheatsheet file where you save everything you’ve tried to solve and more in it it isn’t. This “little” trick can save you a lot of time, especially nerves, because you will have somewhere to go for answers/inspiration. Of course, this entails a requirement for structure and that you need to write it all down, not just solve it and forget it. But trust me, and it’s worth it.

Extraction of Colored Excel Cells

Tue, 13 Jun 2023 11:00:00 +0200

It is also known that the contents of an Excel file can be returned in different types from the Power Query point of view: Sheet, Table, etc., and mainly because of distrust towards the user, we use the mentioned Sheet type to avoid the user skips some row and the dynamic table extension is not applied, so we will not have all the data. But what we get is always “just” the resulting cell value. So you don’t get the calculation procedure, cell color, etc. You won’t get the result even if you query against said Excel directly using OLEDB.

OLEDB agains Excel

Though admittedly, this style of querying Excel also has some merit, as you can perform imaginary “SQL” queries against sheets. And that can be pretty fun (Tables$, Values$ are names of a sheets).

OLEDB with custom query agains Excel

But what to do when we need to get some of these parameters or worse? We need to get values based on that parameter.

Example: we have an Excel where the light yellow cells represent the values the user will fill in, and then we want to retrieve them for reporting purposes.

Ugh. I can hear the question now, so can we prepare some reasonable transformation to get these values out according to some rules? The answer? NO! Because we are looking for a universal way to extract these values. For those marked by writing macros in Excel using VBA, I’m sure more than one option to solve this has come to mind. For example, we could write a VBA script that would export these values based on the color attribute of the cell to, for example, a central Excel spreadsheet that we would then link. It would undoubtedly be the way to go, BUT Excel is not a database, and such a conversion mechanism would only cause more problems. Not to mention that in some companies, macros have been forbidden because of security. Furthermore, we will only work with uniform cell background colors, although similar thinking could be applied to other potentially searchable properties.

So back to Power Query! Is there any possibility? Let’s say yes! Most of the files we usually use, such as “.xlsx,” “.docx,” etc., are compressed folders with content primarily XML or JSON files. Why not compress that Excel and run the individual files through Power Query to find what we need?

Decompress the Excel file

Since we want to build a general procedure that can extract excels independently, we certainly don’t want to manually convert each file to “.zip” and then extract it. We need a procedure that we can use to do this directly from Power Query. Mark White has saved us a lot of work on this in his article Reading ZIP Files in Power Query, as he has already devised and compiled this procedure.

/* UNZIP FUNCTION FROM MARK WHITE LINK TO ARTICLE WITH CODE http://sql10.blogspot.com/2016/06/reading-zip-files-in-powerquery-m.html */
(xlsxFile as binary) =>
    let
        Header = BinaryFormat.Record(
            [
                MiscHeader = BinaryFormat.Binary(14),
                BinarySize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
                FileSize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
                FileNameLen = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
                ExtrasLen = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)
            ]
        ),
        HeaderChoice = BinaryFormat.Choice(
            BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
            each
                if _ <> 67324752
                // not the IsValid number? then return a dummy formatter
                then
                    BinaryFormat.Record([IsValid = false, Filename = null, Content = null])
                else
                    BinaryFormat.Choice(
                        BinaryFormat.Binary(26),
                        // Header payload – 14+4+4+2+2
                        each
                            BinaryFormat.Record(
                                [
                                    IsValid = true,
                                    Filename = BinaryFormat.Text(Header(_)[FileNameLen]),
                                    Extras = BinaryFormat.Text(Header(_)[ExtrasLen]),
                                    Content = BinaryFormat.Transform(
                                        BinaryFormat.Binary(Header(_)[BinarySize]),
                                        (x) =>
                                            try
                                                Binary.Buffer(Binary.Decompress(x, Compression.Deflate))
                                            otherwise
                                                null
                                    )
                                ]
                            ),
                        type binary
                        // enable streaming
                    )
        ),
        ZipFormat = BinaryFormat.List(HeaderChoice, each _[IsValid] = true),
        Entries = List.Transform(
            List.RemoveLastN(ZipFormat(xlsxFile), 1), (e) => [FileName = e[Filename], Content = e[Content]]
        )
    in
        Table.FromRecords(Entries)
// END OF MARK WHITE's UNZIP FUNCTION //

So if we apply it to the “binary” fingerprint of the file we are examining, we have half of the work already done because the “folder” with the content will immediately fall out.

Extracted Excel

So, as a result, we have to orient ourselves and understand what exactly we are looking for to write the appropriate transformation for it.

Structure of the searched problem

This is where the fun starts because Excel works with its cells in different ways, especially in terms of content. From my research, if I open the file of that sheet and try to find some values, I get different kinds of those values. It could be because of reducing the file size, and the text values are all stored together in another file, and from the point of view of the cells themselves, they are only referenced by the index. As a result, one text value does not have to be stored, for example, five times; it simply enters it once into the common XML and puts only its standard index in the cell. Numerical values, on the other hand, are stored directly in the cell. And the difference between whether a cell is text or numeric is known using the XML Attribute “t”, which represents text. If we have a date value, it will be stored as a number, and the calculations are instead stored as two values as the definition of the calculation and the resulting value. Both are within one record.

t Attribute

Another attribute that we should be interested in is “s,” which indicates the style of the character. This is where things get complicated because it is located in the styles file. But here, we can find that we have three different kinds of colors. Indexed colors, RGB, and Theme colors. In reality, however, Excel distinguishes them a little differently:

Excel colors

Depending on which type of color it is, Excel assigns the color to the given style in a slightly different way or that the given color is stored elsewhere. However, we only have their IDs for now. Still no color, but getting close. For the pieces of this puzzle to fit together, you need to find the location of the individual colors:

Index = ./style
RGB = ./style
Theme = ./theme/themeX

Now that we know where the colors are, we can move on to the next problem. The reason is that Excel registers colors as RGB attributes, which is not entirely accurate because, according to the stored value, it is instead a HEX notation of the color, extended by two “FF” characters from the front, in most cases. So we will always have to use Text.Contains().

RGB is not a RGB but HEX with FF as a prefix

The last piece of the puzzle needs to be mentioned! There is a point where Excel won’t save your color as HEX or even as RGB notation. This moment occurs when the user selects a color from the Gradient selection, which is automatically generated from Theme colors. At such a moment, the format will be saved within the styles file as a color ID. But this ID will lead to the original theme color, and the given color adjustment will be made using other attributes (tint, shade).

Usually, this wouldn’t be a problem as it would be enough to convert the color from HEX code to RGB using the following code:

let
    output = (color as text, optional direction as nullable number) =>
        let
            dr = if direction = null then 0 else direction,
            dirList = {0, 1},
            act =
                try
                    let
                        clearedColor = Text.Replace(Text.Upper(Text.Remove(color, {"#", "(", ")"})), "RGB", ""),
                        hexList = {"A", "B", "C", "D", "E", "F"},
                        dir = dirList{List.PositionOf(dirList, dr)},
                        typeOfCalculation =
                            if dr = 0 then
                                let
                                    colorModifier = (hexColorValue as number) =>
                                        let
                                            firstPosition = Number.Mod(hexColorValue, 16),
                                            decreaser = Number.IntegerDivide(hexColorValue, 16),
                                            zeroPosition = Number.Mod(decreaser, 16),
                                            replacer = List.Transform(
                                                {zeroPosition, firstPosition},
                                                each if _ > 9 then hexList{(Number.Mod(_, 9) - 1)} else Text.From(_)
                                            )
                                        in
                                            Text.Combine(replacer, ""),
                                    listSeparator = Text.Split(clearedColor, ","),
                                    transformator = List.Transform(listSeparator, each colorModifier(Number.From(_)))
                                in
                                    "#" & Text.Combine(transformator, "")
                            else
                                let
                                    colorModifier = (decimalColorValue as text) =>
                                        let
                                            split = Splitter.SplitTextByPositions({0, 1})(decimalColorValue),
                                            modifyContent = List.Transform(
                                                split,
                                                each
                                                    let
                                                        vl = _, p = List.PositionOf(hexList, _)
                                                    in
                                                        if p < 0 then
                                                            Number.From(vl)
                                                        else
                                                            p + 10
                                            ),
                                            out = modifyContent{0} * 16 + modifyContent{1}
                                        in
                                            Text.From(out),
                                    listSeparator = Splitter.SplitTextByPositions({0, 2, 4})(clearedColor),
                                    transformator = List.Transform(listSeparator, each colorModifier(_))
                                in
                                    "RGB(" & Text.Combine(transformator, ",") & ")"
                    in
                        typeOfCalculation
                    catch (r) =>
                        if r[Message] = "We couldn't convert to Number." then
                            error
                                [
                                    Message = "Please make sure, that you are inserting correct color type based on your selection in second parameter of this fucntion!"
                                ]
                        else
                            error [Message = "Please insert only these values {0,1} -> 0 = RGB2HEX or 1 = HEX2RGB"]
        in
            act,
    documentation = [
        Documentation.Name = " get-ConverterBetweenHEXandRGB.pq ",
        Documentation.Description = " Converts RGB color code to HEX code and VICE VERSA. Native direction is from RGB to HEX, opposite direction is settable by second parameter of this function. This function can work with standart declaration of these color codes like: ""RGB(.,.,.)"" or ""#FFFFFF"". Accepted enumerations: 0 = RGB2HEX, 1 = HEX2RGB ",
        Documentation.Source = "https://www.datameerkat.com ",
        Documentation.Version = " 1.0 ",
        Documentation.Author = " Štěpán Rešl ",
        Documentation.Examples = {
            [
                Description = " Convert from HEX to RGB *doesnt require second parameter* ",
                Code = " #""get-ConverterBetweenHEXandRGB""(""RGB(123,10,50)"") ",
                Result = "#7B0A32"
            ],
            [
                Description = " Convert from HEX to RGB ",
                Code = " #""get-ConverterBetweenHEXandRGB""(""#7B0A32"",1) ",
                Result = "RGB(123,10,50)"
            ]
        }
    ]
in
    Value.ReplaceType(output, Value.ReplaceMetadata(Value.Type(output), documentation))

Then perform tint or shade color adjustment and conversion back to HEX code.

Color modification

But if you expect it to work in Excel anyway, you will be disappointed. According to all tests, Excel calculates this color change in some way because this standard procedure does not work 100% of the time I do the tests. At the same time, even if I tried this color modification using native VBA code and subsequently extracted the individual colors into RGB notations, the correct color codes did not always come out anyway. We may be able to reveal his approach to coding in the future, but unfortunately, until then, these Gradient colors are unobtainable to us.

Now we know everything we need. So all that remains is to deal with it somehow.

Building procedure and function

Colors are always linked to a style, and each cell has some style assigned. We can think about the problem: We get all the IDs of the colors we seek. Next, we filter all the styles so that we only have those that have a link to one of the colors we are looking for through indexes. From the obtained styles, we will keep only and only their IDs. We can subsequently filter all the cells with them and select only the ones we need. In the end, we’ll check what type of value it is, and if it’s numerical, we’ll save it straight away; for text output, we still have to reach into the text value repository according to the index to get this value.

It sounds wild but doable! I believe that the whole function could be optimized even further, but so far, this was enough for me; where in addition to the searched colors, I also added the possibility of removing some of the colors from the export:

let
    output = (xslxBinary as binary, optional searchedColor as text, optional notSearchedColor as text) =>
        let
            /* UNZIP FUNCTION FROM MARK WHITE
     LINK TO ARTICLE WITH CODE http://sql10.blogspot.com/2016/06/reading-zip-files-in-powerquery-m.html */
            unZipOfXLSX = (xlsxFile as binary) =>
                let
                    Header = BinaryFormat.Record(
                        [
                            MiscHeader = BinaryFormat.Binary(14),
                            BinarySize = BinaryFormat.ByteOrder(
                                BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian
                            ),
                            FileSize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
                            FileNameLen = BinaryFormat.ByteOrder(
                                BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian
                            ),
                            ExtrasLen = BinaryFormat.ByteOrder(
                                BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian
                            )
                        ]
                    ),
                    HeaderChoice = BinaryFormat.Choice(
                        BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
                        each
                            if _ <> 67324752
                            // not the IsValid number? then return a dummy formatter
                            then
                                BinaryFormat.Record([IsValid = false, Filename = null, Content = null])
                            else
                                BinaryFormat.Choice(
                                    BinaryFormat.Binary(26),
                                    // Header payload – 14+4+4+2+2
                                    each
                                        BinaryFormat.Record(
                                            [
                                                IsValid = true,
                                                Filename = BinaryFormat.Text(Header(_)[FileNameLen]),
                                                Extras = BinaryFormat.Text(Header(_)[ExtrasLen]),
                                                Content = BinaryFormat.Transform(
                                                    BinaryFormat.Binary(Header(_)[BinarySize]),
                                                    (x) =>
                                                        try
                                                            Binary.Buffer(Binary.Decompress(x, Compression.Deflate))
                                                        otherwise
                                                            null
                                                )
                                            ]
                                        ),
                                    type binary
                                    // enable streaming
                                )
                    ),
                    ZipFormat = BinaryFormat.List(HeaderChoice, each _[IsValid] = true),
                    Entries = List.Transform(
                        List.RemoveLastN(ZipFormat(xlsxFile), 1),
                        (e) => [FileName = e[Filename], Content = e[Content]]
                    )
                in
                    Table.FromRecords(Entries),
            // END OF MARK WHITE's UNZIP FUNCTION //
            // Suport Functions //
            themeColors = (themeBinary as binary) =>
                let
                    schemaColorList =
                        let
                            initTbl = Table.Combine(
                                Record.ToList(
                                    Table.RemoveColumns(
                                        Xml.Tables(themeBinary)[themeElements]{0}[clrScheme]{0}, "Attribute:name"
                                    ){
                                        0
                                    }
                                )
                            ),
                            extraction = Table.AddColumn(
                                initTbl,
                                "clrs",
                                each
                                    (
                                        if [sysClr] <> null then
                                            [sysClr]{0}[#"Attribute:lastClr"]
                                        else
                                            [srgbClr]{0}[#"Attribute:val"]
                                    )
                                        & "00"
                            )[clrs]
                        in
                            extraction
                in
                    schemaColorList,
            stylesIDs = (stylesXML as binary, themeColors as list) =>
                let
                    Source = Xml.Tables(stylesXML){[Name = "fills"]}[Table]{0}[fill],
                    expandPatternOfFill = Table.ExpandTableColumn(Source, "patternFill", {"fgColor"}, {"fgColor"}),
                    expandBackGroundColor = Table.ExpandTableColumn(
                        expandPatternOfFill,
                        "fgColor",
                        {"Attribute:indexed", "Attribute:rgb", "Attribute:theme"},
                        {"indexed", "rgb", "theme"}
                    ),
                    indexedColorsList = List.Buffer(
                        Table.ToList(
                            Xml.Tables(
                                stylesXML{[Name = "styles.xml"]}[Content]
                            ){
                                [Name = "colors"]
                            }[Table]{
                                [Name = "indexedColors"]
                            }[Table]{
                                0
                            }[Table]
                        )
                    ),
                    indexCreator = Table.AddIndexColumn(expandBackGroundColor, "Index", 0, 1, Int64.Type),
                    filterForSpecificColor =
                        if searchedColor <> null then
                            let
                                colorSeeker = Table.AddColumn(
                                    indexCreator,
                                    "colorSeeker",
                                    each
                                        try
                                            if [indexed] <> null then
                                                indexedColorsList{Number.From([indexed])}
                                            else if [rgb] <> null then
                                                [rgb]
                                            else
                                                themeColors{Number.From([theme])}
                                            otherwise null
                                )
                            in
                                Table.SelectRows(colorSeeker, each Text.Contains([colorSeeker], searchedColor))
                        else if notSearchedColor <> null then
                            let
                                colorSeeker = Table.AddColumn(
                                    indexCreator,
                                    "colorSeeker",
                                    each
                                        try
                                            if [indexed] <> null then
                                                indexedColorsList{Number.From([indexed])}
                                            else if [rgb] <> null then
                                                [rgb]
                                            else
                                                themeColors{Number.From([theme])}
                                            otherwise null
                                )
                            in
                                Table.SelectRows(
                                    colorSeeker,
                                    each
                                        not Text.Contains([colorSeeker], notSearchedColor) and [colorSeeker] <> null
                                )
                        else
                            let
                                nonNullCounter = Table.AddColumn(
                                    indexCreator,
                                    "counter",
                                    each List.NonNullCount(Record.ToList(Record.RemoveFields(_, "Index")))
                                )
                            in
                                Table.SelectRows(nonNullCounter, each [counter] <> 0)
                in
                    filterForSpecificColor[Index],
            XfsIDs = (stylesXML as binary, listOfStyles as list) =>
                let
                    Source = Xml.Tables(stylesXML){[Name = "cellXfs"]}[Table][xf]{0}[[#"Attribute:fillId"]],
                    indexCreator = List.Transform(
                        Table.SelectRows(
                            Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
                            each List.Contains(listOfStyles, Number.From([#"Attribute:fillId"]))
                        )[Index],
                        each Text.From(_)
                    )
                in
                    indexCreator,
            valueExtract = (workheetBinaries as binary, XfsSearchingIds as list, dt as list) =>
                let
                    sheetExtraction = Xml.Tables(workheetBinaries){[Name = "sheetData"]}[Table][Table]{0}[[c]],
                    expandSheetData = Table.SelectRows(
                        Table.AddColumn(
                            Table.ExpandTableColumn(
                                sheetExtraction,
                                "c",
                                {"v", "Attribute:r", "Attribute:s", "Attribute:t"},
                                {"v", "r", "s", "t"}
                            ),
                            "search",
                            each let s = _[s] in List.Select(XfsSearchingIds, each _ = s){0} ?
                        ),
                        each [search] <> null
                    )[[v], [t], [search], [s], [r]],
                    output = Table.AddColumn(
                        expandSheetData,
                        "val",
                        each
                            if [t] = null then
                                [v]
                            else
                                try dt{Number.From([v])}[#"Element:Text"]{0} otherwise dt{Number.From([v])}
                    )
                in
                    output[[val], [r]],
            sharedStrings = (shString as binary) => let Source = Xml.Tables(shString)[si]{0}[t] in Source,
            //Data Extraction
            unzipingXLSX = unZipOfXLSX(xslxBinary),
            themes = List.Buffer(themeColors(unzipingXLSX{[FileName = "xl/theme/theme1.xml"]}[Content])),
            styles = List.Buffer(stylesIDs(unzipingXLSX{[FileName = "xl/styles.xml"]}[Content], themes)),
            xfs = List.Buffer(XfsIDs(unzipingXLSX{[FileName = "xl/styles.xml"]}[Content], styles)),
            data = List.Buffer(sharedStrings(unzipingXLSX{[FileName = "xl/sharedStrings.xml"]}[Content])),
            extraction =
                let
                    generator = Table.RenameColumns(
                        Table.SelectRows(
                            Table.TransformColumns(
                                Table.SelectRows(
                                    unzipingXLSX,
                                    each
                                        Text.Contains([FileName], "worksheets")
                                        and Text.End([FileName], 5) <> ".rels"
                                ),
                                {
                                    {"FileName", each Text.BetweenDelimiters(_, "xl/worksheets/", ".xml")},
                                    {"Content", each valueExtract(_, xfs, data)}
                                }
                            ),
                            each not Table.IsEmpty(_[Content])
                        ),
                        {"FileName", "Sheet"}
                    ),
                    expander = Table.TransformColumnTypes(
                        Table.ExpandTableColumn(generator, "Content", {"val", "r"}, {"Value", "Position"}),
                        { {"Sheet", type text}, {"Value", type text}, {"Position", type text} }
                    )
                in
                    expander
        in
            extraction,
    documentation = [
        Documentation.Name = " get-ColoredExcelCells.pq ",
        Documentation.Description = " Function for extracting colored cells from Excel file. ",
        Documentation.Source = "https://www.datameerkat.com ",
        Documentation.Version = " 1.0 ",
        Documentation.Author = " Štěpán Rešl "
    ]
in
    Value.ReplaceType(
        output,
        Value.ReplaceMetadata(
            Value.Type(output),
            documentation
        )
    )

I want to test it, though! So here I have an Excel demo with two worksheets, different values placed, and different colors.

Demo Excel

Let’s try using the function mentioned above to get all the cells with the color “#ABD88C”:

Extracted values

YES!!! We have all cells with #ABD88C background color! Only dates look like numbers. (As always)

Summary

I would dare to summarize it with a classic message: “Don’t try this at home.” But that wouldn’t be true! It was a challenge that Tomáš Prokop came to me with, and I’m glad that I could crack this nut. Of course, there are better-looking and more straightforward options than solving it using Power Query, but if there is no other option, it’s certainly good to know that it can be done. In general: “Don’t say something can’t be done, or someone who doesn’t know will come and do it.” Like for example, this. I’ve often heard that it can’t be done, and the opposite is true. It won’t hurt if it’s about personal analysts or a one-time export. But using something like that and a production solution is playing with fire, and you can get very burned.

The function in this state returns ONLY the cells marked with a color. If you would like a different variant that would return, for example, a cell next to it, then the code needs to be slightly modified, but of course, it would also be possible!

The last thing I would like to add is: Be bold and try to break new ground. At most, you will find it impossible in that direction. It is still a valuable lesson; you won’t go down that road again. But if you could, and it turned out to be ineffective, you can still learn a lot. That’s why it’s good to doubt, experiment and try to discover new ways, and don’t be afraid to make mistakes when you’re learning. So then, when you need it, you will know a way to solve it.

Data Meerkat

Fabric Notification Bot for Microsoft Teams

Reflection on the Problem

Direction

Creating the Owl Bot

Creating an Azure Bot

Creating an Azure Web App

The Brain of the Web App Bot

API Permissions for Bot Service Principal

Creating a Microsoft Teams App

Obtaining the Chat ID

Testing the Bot

Fabric Notebook

Conclusion

Write your data into Lakehouse by Notebooks

Default Lakehouses in Notebook

Switching Default Lakehouse by Configuration

Switching Default Lakehouse by notebookutils

Writing data into Lakehouse by PySpark

Writing data into Lakehouse by Python

Orchestration Fabric Notebooks

Orchestration

Shared resources

Spark Sessions

Orchestration of Notebooks

NotebookUtils for orchestration

Functions for orchestration

%run magic command:

Supported DAG for Notebooks

DAG Validation

Conclusion

GraphQL in Microsoft Fabric

Supported Fabric Items

Connecting Data Sources to GraphQL

Creating Relationships Between Tables

Querying Data

Executing Mutations and Stored Procedures

Variables

Conclusion

Combining Calculation Groups

Power BI Templates

Visual templates

Theme templates

Online templates

Data templates

PBIDS Files

Model.json

Power Query Templates

Combination templates

Power BI Template Files (PBIT) + Power BI Projects

Community solution

Copy Activity, Dataflows Gen2, and Notebooks vs. SharePoint Lists

Guardrails and assumptions

Copy Activity

Dataflows Gen2

Notebooks

Comparison

Personal Workspaces in Times of Fabric

Personal Workspaces and Their Purpose

Types of Personal Workspaces

Self-Service BI and Personal Workspaces

Possibilities and Settings of Personal Workspaces

Fabric related possibilities

Assigning a Personal Workspace to a Capacity

Boosted capabilities of personal workspaces with capacity

Auditing, Accessing, and restoring personal workspaces

Summary

Lessons learnt from PySpark Notebooks and extracting APIs

Introduction to Fabric PySpark notebooks

How to get data from API

Saving data to LakeHouse

Spark operations vs. non-Spark operations

Follow-up API requests and pagination

Follow-up API requests

Pagination

Summary

Extraction of Colored Excel Cells

Decompress the Excel file

Structure of the searched problem

Building procedure and function