Forum Discussion

BiJoe's avatar
BiJoe
Helper II
2 years ago

Fabric migration: architecture

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. 

Dataflows

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    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's avatar
      BiJoe
      Helper II
      2 years ago

      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?