How to create a PreFilter Dimension in SSAS

In this tutorial we will see how we can filter Dimension Data and then use it in cube. Here we will filter data of dimension DimPromotion.

• Open your DSV then right click on middle window and select option "New Named Query" as in the image below

• Now in the Create Named Query window write your query, here we are filtering the data where promotion is not equal "No Discount", Give name to the new dimension "DimPromotionNew" and then click Ok.

                 SELECT *

                  FROM [dbo].[DimPromotion]

                     where EnglishPromotionName<>'No Discount'

• Now you will find a new dimension created in your dsv "DimPromotionNew". Now create relationship with your facts with this newly created dimension and delete the old one.
• If you explore the data in these two dimensions then you will find the difference of 1 row, in the new dimension the row with the Promotion "No Discount" is deleted.

DimPromotion Data

DimPromotionNew Data

Cheers,