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) Lake...
Dataflows
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?