Reduce Records Shown From Dimension

Hi There,

I'm relatively new to AS2005, so you'll have to excuse me if there is a simple solution that has been overlooked.

Here is the situation - We have a fact table, linked to several dimensions - one of them being a generic date dimension used by a number of fact tables. The Date Dimension has all dates ranging from 1901 to 2100, but the fact table I am querying just has records from 2005 to present. Once the cube is processed and I open the cube in Excel (for example) it lists every date from the Date Dimension, is there any way to limit which dates are processed into the cube to those that just appear in the fact table (in essence - what would be an Inner Join in TSQL).

Hope this makes sense,

Regards,

Tobias

Are you using the dimension on rows or columns? Or as a filter?

If it is rows or columns you change the setting to not show empty values.

If it is a filter, you could create a set that only contains only those dates that has rows. Regardless, you could probably remove a lot of dates from the time dimension that is not used by any fact table and/or create a view that adds dates as time goes by.|||

Instead of telling the cube (via the data source view) to read directly from the time dimension table, we tend to use db views that restrict the members to only those members that appear in the fact table. You can change the dsv to read from the view and you will then only get dates that appear in the fact table.

However, with the time dimension, you want to make sure you fill in all dates of a year -- if you don't you can't really do year-over-year comparisons reliably. So, perhaps you can have the view return all dates from your time dimension table that start AFTER the first date in your fact table. You can use similar logic to restrict the dates on the high end as well.

|||

So does this mean, for each fact table - you have a different view set up?

We have about 15-20 cubes that we want to set up which all use the time dimension, with varying volumes of historical data from each of the source systems. Is it best practise to then limit the data returned by the dimension for each group of cubes?

I was hoping that there would be a technique/feature that prevented 'redundant' (i.e. rows that do not have a related fact record) dimension members from appearing in the cube, that way we could begin to build the suite of cubes in a smaller number of projects and reduce administrative overheads (by having several Time Dim views for different date ranges for example)

Regards,

Tobias

|||

Well, our one view looks at all of our various fact tables to determine which entries are needed.

The problem you have is that Excel will list out all dimension members, so I'm not sure what the best solution is for you. But I don't think there is magic property that you can tell Excel to list only dimension members that are only for a particular cube.

|||Is it Excel 2007 you use? Because then you can create a named set that only contains dates that are non empty, has rows in the fact table connected to it. I′m not sure if Excel 2003 can use sets.|||We're using Excel 2003. shame there isn't a feature that does that.|||

Even in Excel 2003 pivot table, you have the option to hide the members that do not have data.

As you are using time dimension in different measure groups, the best practice would be to create hierarchy in the date dimension like Year ==> Month ==> Date levels that would make the navigation easier for the users. There are lot of articles on ways to handle it. Pl refer to BOL and newsgroups.