SSAS Date Field in None Date Dimension

0
votes

I have issue trying to filter in excel 2010 pivot table on date attributes from a Cube source.

My problem is i have Date attributes in NONE date dimensions my example is a Asset Dimension that has a set of date attributes like:

  • Bought date

  • Sold Date

  • Repair Date

Is there a way to set these attributes to type date so pivot table will directly identify them and allow use of Date Filters ?

Or do i have to create a Date dimension for each attribute and user will have to select which Dimension he wants to slice data by

1
datessasexcel-2010pivot-table
I do not think that it is possible to get the attribute members as dates, no matter if the dimension is marked as date or not. You can access the members keys as a date in MDX expressions using an expression like [Asset].[Bought date].CurrentMember.Properties('Key0', typed). But I do not think you can use this in client tools like Excel to use date filters. – FrankPl
hmm so far looks that way, i foudn a little work a round by setting the value columns of date time attribute to it self, using the label fields filter like between and greater than. But i cant seem to edit the format of the attribute now, since it seems having the date format is also needed for excel to allow the filtering to work and adding a name column ruins that. So far very annoying process – ame54
I commented on your post over on MSDN, but here's something new I found today: the AdventureWorks demo cube shows the same problem. When you use the Date Dim, Excel includes date filters. But several non-date dimensions include date attributes (e.g. Promotion has a Start Date and End Date) and when you include them in an Excel pivot table, they are treated as strings rather than dates. You don't get date filters, and if you sort them they sort alphabetically. – MattClarke

1 Answers

0
votes

One option is to add an additional date "role-playing" dimension to the cube (e.g. DimAssetBoughtDate) and hang it off the fact tables using a reference-relationship through the Asset dimension. This not only provides you with the date-based filtering but also all of the built in hierarchies. Plus date dims are usually pretty small so materializing the relationship shouldn't be an issue.

Note: you may need to add a calculated member to your Asset dimension in your DSV to convert the date value (e.g. AssetBoughtDate 3/12/2015) to the date-key value (e.g. AssetBoughtDateKey 20150312) in order to properly add the new DimAssetBoughtDate.