Forum Discussion
Pre-filter Fact Tables for reporting
Hi there,
quick update on this.
We eventually went with the option of creating duplicate tables. We followed the following steps to ensure data integrity:
For the warehouse tables we appended a CTAS statement to the stored procedure which handles the transformations to the gold layer. The CTAS select data from the complete gold table and applies the desired filtering. Appending this to the stored procedure ensure that we are selecting the most recent version of the data.
We are hoping that the additional storage costs will be justified by faster queries on the filtered fact tables.
Hi ObungiNiels
Thank you for reaching out microsoft fabric community forum.
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi there,
quick update on this.
We eventually went with the option of creating duplicate tables. We followed the following steps to ensure data integrity:
For the warehouse tables we appended a CTAS statement to the stored procedure which handles the transformations to the gold layer. The CTAS select data from the complete gold table and applies the desired filtering. Appending this to the stored procedure ensure that we are selecting the most recent version of the data.
We are hoping that the additional storage costs will be justified by faster queries on the filtered fact tables.
Hi ObungiNiels
Good to hear that you found a workaround. Please accept it as a solution so that it might be helpful for other community members with similar problems to solve it faster.
Thank you.