1

Wondering if it's possible to remove a Time Dimension entry such as "DEC-2014" from SSAS cube after it's processed.

An Oracle lookup table provides Time dimension values, but data for each corresponding month might not be available yet. Would like to manually remove Time dimension after processing, but not delete from underlying Oracle table.

Time
 -> All Time
    -> 2014
        -> Q1 2014
            -> JAN-2014
            -> FEB-2014
            -> MAR-2014
        -> Q2 2014
            -> APR-2014
            -> MAY-2014
            -> JUN-2014
        -> Q3 2014
            -> JUL-2014
            -> AUG-2014
            -> SEP-2014
        -> Q4 2014
            -> OCT-2014
            -> NOV-2014
            -> DEC-2014 (Would like to delete this entry)
user2966445
  • 1,267
  • 16
  • 39
  • 1
    maybe instead of looking how to delete, google around filtering dimension data in the security roles of SSAS. It might be an easier option – mxix Nov 07 '14 at 15:17

1 Answers1

0

Do this in the data source view (DSV) of the project. If your data source table is currently coming through as a table, right click on the date dimension table in the DSV and choose to "Replace Table>With New Named Query..." and set your where clause to the following:

WHERE YEAR(DateField)*100 + MONTH(DateField) <= YEAR(GETDATE())*100 + MONTH(GETDATE())

This will exclude all months after the current month from being in your date dimension.

Keep in mind, if you have values in the excluded months in your fact table with foreign keys to your date dimension, this will cause an error on processing when the key is not found. I would alter your fact table ETL to not accept rows past the current month as well to account for this possibility.

EDIT: Another possible solution is:

WHERE DateKey BETWEEN (SELECT MIN(DateKey) From FactTable) AND (SELECT MAX(DateKey) From FactTable)

This one works if you don't have any records in your fact table for the offending months.

Michael
  • 1,556
  • 13
  • 25