Fabric migration: architecture

[BiJoe]

We currently have a DataFactory V2 running, but have also started to explore Fabric. Current Fabric WIP architecture:

FundamentalWorkSpace

Warehouse:

• Date dimension SQL table (existing)

Lakehouse:

• Shortcut to date dimension SQL table in warehouse (existing)

Reason for both lakehouse and warehouse, is cross workspace access to lakehouse data.

SalesOfferWorkspace

KQL DB:

• SalesOfferDimension table (existing KQL table, data from pipeline)
• SalesOfferRaw (existing KQL table, data from pipeline)

To be decided:

• SalesOfferFact table

In the old dataplatform, all dimensions and facts are gathered in one big semantic PBI model/ dataset. Starting fresh in Fabric, I would like to experiment with isolating concepts in workspaces, for self-serving PBI report building etc.

There are so many architectural options in Fabric. How or where would you create the SalesOfferFacts table, given that it has to join sales offers, dates, accounting periods etc?

EDIT: the contributors in the SalesOffer workspace are citizen (?) PBI report builders, they just need easy access to a star schema semantic model from PBI Desktop. 

[amitchandak]

@BiJoe ,

Date dimension - You can build on DataFlow Gen 2 or can get from source

Microsoft Fabric: Create Date table Dataflow Gen2, use in Lakehouse, Warehouse| Time Intelligence: https://youtu.be/g3BEVXQdk-g

microsoftfabric/powerbi- Time Intelligence with Multi-Company Calendar/Date table| Duplicate Date: https://youtu.be/rIYc4IK-1ZY

In Our case we are using Warehouse and its dataset for Power Bi Source.

Reason- We prefer SQL for transformation

Power Query Transformation can slow down large data loads.

We do not want to use spark for trsnformation

This is how we are doing it

Source -> Lakehouse(Backup/Stg) -> Warehouse -> Power BI

When Lakehouse and Warehouse are in the same workspace, you can treat them like two SQL server DB and work

Mastering Microsoft Fabric 35+ Videos: https://www.youtube.com/watch?v=p-v0I5S-ybs&list=PLPaNVDMhUXGYU97pdqwoaociLdwyDRn39&index=1

[BiJoe]

@amitchandak thanks for replying. Sorry for not giving full picture in original post, have edited with more info, the date dimension exists as a SQL table in a warehouse. 

I appreciate your take on Power Query performance, also interesting that you ingest data to lakehouse first, then to warehouse. Does the SQL analytics endpoint not satisfy your need?

I am kind of leaning into using a lakehouse as general "data endpoints" in the respective workspaces, due to cross workspace shortcuts. What I am not sure of, is the sanity of siloing the data into multiple workspaces, particularly regarding app data access across workspaces.

The primary benefits of multiple workspaces seems to be access governance, and in some of our use cases that makes sense, for example in the Sales Offer data example. Still, any report or app in those workspaces will need easy access to date dimensions etc, preferrably preserving the "one copy of data" principle in OneLake. Any thoughts about this?

[v-shex-msft]

Hi @BiJoe,

Perhaps you can take a look at the fabric notebook features, it allow you to use simple queries to handle with different type of data sources.

How to use notebooks - Microsoft Fabric | Microsoft Learn
Regards,

Xiaoxin Sheng