Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RRaj_293
Helper I
Helper I

Datamart layer between Database and Dataset

Hi All , 

 

We are building a Datamart layer inorder to avoid the datasets refreshing the same common tables multiple times.

 

current arch-

Database(snowflake) - Datasets - PBI reports

Proposed -

Database(snowflake) - Datamart - Datasets - PBI reports

 

We have around 7-8 datasets (on which multiple reports are built) hitting the database directly . We are building a datamart layer between DB and these datasets so that the tables common in these datasets can be moved to datamart and hence instead of the same tables hitting the DB three/four times it gets refreshed once in- the datamart layer and then caters to the datasets as we replace these tables to point to DM instead of Database.

 

Questions-

1. If a dataset has 10 tables and only 5 are replaced with the ones in Datamart , when the dataset is refreshed(post DM refresh) will the tables pointing to datamart refresh again or not? Only the tables pointing to DB refreshes?

 

2. How do we change the settings to point the datasets from DB to datamart?

 

3. How about the folder structures and hierachies specific to datasets ? Would they have to be recreated in the DM as well?

 

3. If a fact table(Ex- fact sales) common across many datasets has measures specific to the dataset and cannot be moved to the datamart fully with all measures across all datasets , I will not be able to replace the full table with the datamart. Some calculations will still be specific to the dataset. In such cases how does the refresh work? After table is refreshed in Datamart , will calcualtions in the dataset be refreshed again during dataset refresh ?

 

I'm assuming the table will be repointed to the Datamart and those calcualtions which are specific to the Dataset will have to be recreated in the dataset and replace only the common ones created in DM?That way , I will still save some refresh time ?

 

Please advise.

 

Thanks

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@RRaj_293 ,

1. You need to setup incremental load, check option detect changes if needed

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh

 

2. As database is the same, edit connection under datasource setting should do , or manual explained below

https://www.youtube.com/watch?v=59PUFuuCrbY&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L

 

3. if you only change the source and table structure is the same, there will be no change

 

4. Not fully gotten it. But Measures like A/B , using row calculations need to be in power bi only

View solution in original post

2 REPLIES 2
RRaj_293
Helper I
Helper I

Thank you .

 

amitchandak
Super User
Super User

@RRaj_293 ,

1. You need to setup incremental load, check option detect changes if needed

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh

 

2. As database is the same, edit connection under datasource setting should do , or manual explained below

https://www.youtube.com/watch?v=59PUFuuCrbY&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L

 

3. if you only change the source and table structure is the same, there will be no change

 

4. Not fully gotten it. But Measures like A/B , using row calculations need to be in power bi only

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors