Edit

How to build translytical task flows with Cosmos DB in Microsoft Fabric

  • Applies to: ✅ Cosmos DB in Microsoft Fabric

Translytical task flows combine operational and analytical workloads by connecting Cosmos DB in Microsoft Fabric, User Data Functions, and Power BI. You can build interactive reports that read data from Cosmos DB and write updates back in real time through User Data Functions.

Note

Translytical task flows are currently in public preview.

A translytical task flow uses three components:

Component Purpose Technology
Operational data store Real-time access to operational data for analytics Cosmos DB in Fabric
Data logic layer Execute business logic such as writing data back to the data store User Data Functions
Visualization layer Display data and provide interactive controls Power BI

Tip

For a complete sample of a translytical task flow with Cosmos DB in Fabric, see Translytical task flow sample on GitHub.

Prerequisites

Retrieve Cosmos DB endpoint

Get the endpoint for the Cosmos DB database in Fabric. This endpoint is required to connect the user data function to Cosmos DB.

  1. Open the Fabric portal (https://app.fabric.microsoft.com).

  2. Navigate to your existing Cosmos DB database.

  3. Select the Settings option in the menu bar for the database.

    Screenshot of the 'Settings' menu bar option for a database in the Fabric portal.

  4. In the settings dialog, navigate to the Connection section. Then, copy the value of the Endpoint for Cosmos DB NoSQL database field. You use this value in later steps.

    Screenshot of the 'Connection' section of the 'Settings' dialog for a database in the Fabric portal.

Create and publish a User Data Function

Create a User Data Function that updates the current price of a product in the Cosmos DB database.

  1. In your workspace, select + New Item.

  2. Search for user data functions and select the tile.

    Screenshot showing user data functions tile in the new item pane.

  3. Provide update_price_writeback as the Name for your User Data Function.

  4. On the User Data Function editor page, select New function to create a hello_fabric user data function template.

    Screenshot showing how to create a new function using a template.

  5. Copy the code below and paste it into the editor, replacing the default template code:

    import fabric.functions as fn
udf = fn.UserDataFunctions()

import logging
from datetime import datetime
from typing import Any
from azure.cosmos import CosmosClient
from azure.cosmos import exceptions

COSMOS_URI = "YOUR_COSMOS_DB_URI_HERE"
DB_NAME = "YOUR_DATABASE_NAME_HERE"
CONTAINER_NAME = "SampleData"

@udf.connection(argName="cosmosClient", audienceType="CosmosDB", cosmos_endpoint=COSMOS_URI)
@udf.function()
def update_price(cosmosClient: CosmosClient, categoryName: str, productId: str, newPrice: float) -> list[dict[str, Any]]:

    try:
        database = cosmosClient.get_database_client(DB_NAME)
        container = database.get_container_client(CONTAINER_NAME)

        product = container.read_item(item=productId, partition_key=categoryName)

        product["currentPrice"] = newPrice

        now = datetime.now().replace(microsecond=0)
        current_time_iso = now.isoformat()

        product["priceHistory"].append({
            "date": current_time_iso,
            "price": newPrice
        })

        container.replace_item(item=productId, body=product)

        return product

    except exceptions.CosmosResourceNotFoundError as e:
        logging.error(f"Item not found in Cosmos DB: {e}")
        raise
    except exceptions.CosmosHttpResponseError as e:
        logging.error(f"Cosmos error in update_price: {e}")
        raise
    except Exception as e:
        logging.error(f"Unexpected error in update_price: {e}")
        raise

  6. Replace YOUR_COSMOS_DB_URI_HERE with the endpoint URI you copied earlier.

  7. Replace YOUR_DATABASE_NAME_HERE with the name of your Cosmos DB database.

  8. In the top menu bar, select Library Management, then choose + Add from PyPI. Search for azure-cosmos, select it from the results, and pick the latest version.

    Screenshot showing how to add azure-cosmos library.

    Close the library management pane once the library is added, and on the top menu bar, select Publish.

    Screenshot of the Publish button on the top menu bar.

  9. Hover over the function name in the left pane after publishing completes, and select the Test icon.

    Screenshot of the Test icon for the published function.

  10. Provide sample input values in the test pane:

    • categoryName: Computers, Desktops
    • productId: 05bca58f-257c-4129-8373-1b0951cb8104
    • newPrice: 5000

  11. Select Test to run the function. Review the output and logs to verify the function runs successfully, then close the test pane.

Connect to Cosmos DB data in Power BI

Connect to your Cosmos DB database from Power BI Desktop and transform the data for your report.

  1. Open Power BI Desktop and create a new report.

  2. Select Get Data > More, search for Cosmos DB, and select Azure Cosmos DB v2. Select Connect.

    Screenshot of the Get Data dialog with the Azure Cosmos DB v2 connector selected.

  3. In the connection dialog, provide the Cosmos DB endpoint and for Data Connectivity mode, select DirectQuery. Select OK.

    Note

    If prompted for authentication, select Organizational account, sign in with your Microsoft Fabric credentials, and select Connect.

  4. In the Navigator pane, expand the Cosmos DB database and select the SampleData and SampleData_PriceHistory[] tables from the SampleData container.

  5. Select Transform Data to open the Power Query Editor.

    Screenshot of the Navigator pane with SampleData tables selected.

  6. In the Power Query Editor, rename the SampleData_priceHistory[] table to PriceHistory by right-clicking the table in the left pane and selecting Rename.

  7. Select the SampleData table. On the Home tab, select Choose Columns and keep only these columns: categoryName, currentPrice, docType, name, and productId. Select OK.

    Screenshot of the Choose Columns dialog with the selected columns.

  8. Select the docType column in the SampleData table and filter to only include rows where docType equals product.

  9. Select the PriceHistory table and remove the following columns: SampleData(categoryName), SampleData(id), and categoryName by right-clicking the column headers and selecting Remove.

  10. Rename SampleData_priceHistory[]_date to date and SampleData_priceHistory[]_price to price.

  11. Select Close and Apply to load the data into Power BI.

Create the interactive report

Build report visuals and connect the User data function to enable price updates from the report.

  1. Switch to the Model view in Power BI Desktop and select Manage Relationships from the top menu.

    Screenshot of the Manage Relationships option in the Model view.

  2. Select New Relationship and create a relationship between the productId field in the SampleData table and the id field in the PriceHistory table. Select Save and close the Manage Relationships dialog.

    Screenshot of the New Relationship dialog with the productId and id fields mapped.

  3. Select the SampleData table in the Data pane. On the top menu, select New measure.

  4. Copy the DAX code below and paste it into the formula bar, then select the checkmark icon to save the measure. This measure formats the current price for display in the report.

    currentPriceDisplay =
VAR v = SELECTEDVALUE('SampleData'[currentPrice])
RETURN
IF(
    HASONEVALUE('SampleData'[categoryName]) &&
    HASONEVALUE('SampleData'[name]),
    IF(
        v >= 100000,
        "$" & FORMAT(v / 1000, "#,0.##") & "K",
        "$" & FORMAT(v, "#,0.##")
    ),
    BLANK()
)

  5. Switch to Report view on the left navigation bar.

  6. In the Visualizations pane on the right, select the Slicer visual and add the categoryName field from the SampleData table.

    Screenshot of the category name slicer visual on the report canvas.

  7. In the Format Visual tab, under Slicer Settings, change the Style to Dropdown.

    Screenshot of the slicer format settings with Dropdown style selected.

  8. Add another Slicer visual for the name field from the SampleData table.

  9. Add a Card visual and drag the currentPriceDisplay measure to the Value well.

    Screenshot of the card visual showing the current price display measure.

  10. Toggle off Label in the Format Visual tab under Visual > Callout.

  11. Toggle on Title under General and set the title text to Current Price.

  12. Add a Line Chart visual on the report canvas.

  13. Drag the date field from the PriceHistory table to the X axis well and the price field to the Y axis well.

    Screenshot of the line chart visual showing the price history over time.

  14. Add an Input Slicer visual to the report canvas.

    Screenshot of the input slicer visual on the report canvas.

  15. Select the input slicer and in the Format Visual > General > Title options, set the title to Enter new price.

  16. From the top navigation bar, select Insert > Buttons, and in the dropdown, select Blank to add a blank button to the report.

    Screenshot of the Insert menu with the Blank button option.

  17. Position the button below the input slicer.

  18. Select the button and in the Format button pane, expand Action and turn it On.

  19. Configure the Action section with the following values:

    • Type: Data function
    • Data function: Select the fx button, expand update_price_writeback, and select update_price. Select Connect.

  20. Map the function parameters:

    • categoryName: Select the fx button and select the categoryName field from the SampleData table.
    • productId: Select the fx button and select the productId field from the SampleData table.
    • newPrice: Select Enter New Price from the dropdown.

    Screenshot of the Action section with function parameters mapped to report fields.

  21. In the Format button pane, expand Style, turn Text on, and set the button text to Submit.

  22. In Style > Apply settings to, switch the State to Loading. Expand Text and set the value to Submitting.

Publish and run the task flow

  1. Save your Power BI report and select Publish from the Home tab to publish the report to your Fabric workspace.

  2. Navigate to your Fabric workspace and open the published report.

    Note

    On first opening, you might encounter an error: The data source Extension is missing credentials and cannot be accessed. To resolve this:

    1. Open the semantic model for your report and from the top menu bar, select Settings.
    2. Expand the Data source credentials setting.
    3. Select Edit credentials.
    4. Select OAuth2 as the Authentication method.
    5. Select Sign in and sign in with your Microsoft Fabric credentials.

  3. In the input slicer, enter a new price for the selected product.

  4. Select Submit to invoke the User Data Function and update the product price in Cosmos DB.

  5. After the function completes, the current price card and price history line chart update to reflect the new price. If the visuals don't refresh automatically, select the refresh button in the top menu bar.

    Screenshot of the completed translytical task flow report with slicers, price card, line chart, and submit button.

Related content

  • Last updated on