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
