Format of Shared Dimension Data... 1

[rheilman]

I've done a search on this and the results weren't about the current challenge so I'll try posting. If there is a better place for this question, please direct me there.


I have an OLAP Cube built from three shared dimensions. When I reference the OLAP Cube from an Excel XP spreadsheet, the individual fields from one of the dimensions are inaccessible except from the "Hide/Show Details" commands in the pivot table. The problem is that the format of the DateTime field from the Cube/Dimension includes the zeroes in the Time portion. I'm looking for a way to make this presentable (ie: MM/DD/YYYY) in the pivot table and resulting pivot chart.

The paradox is that the dimensions and cubes appear to deal with data and not formats. In Excel where formatting should be addressed, I can't seem to "touch" the data to format it.

Any ideas?

Thanks!
Ray <><

 

[rheilman]

Okay, here's a possible solution that I've found. In the Basic tab of the Properties for the Date field in the Dimension Editor, two of the properties are Member Key Column and Member Name Column. My understanding of these is that the Key column is dealing more with the actual data, and the Name column deals more with presentation. I look forward to hearing this put more simply from someone with more experience at this.

My value for Member Key Column is the one that came by default when I built the dimension...

"dbo"."Organization_Calendar"."Date"

My value for the Member Name Column is...

LTRIM(
RTRIM(
CAST(MONTH("dbo"."Organization_Calendar"."Date")
AS VARCHAR)))
+ '/'
+ LTRIM(
RTRIM(
CAST(DAY("dbo"."Organization_Calendar"."Date")
AS CHAR)))
+ '/'
+ LTRIM(
RTRIM(
CAST(YEAR("dbo"."Organization_Calendar"."Date")
AS CHAR)))

So far it appears to present the data the way we want, while not changing the grouping and aggregation on the pivot or the chart.

Thanks!
Ray <><

 

[rheilman]

Better still, Member Name Column is...

CONVERT(CHAR(8), "dbo"."Organization_Calendar"."Date", 1)

...compliments of my boss.

Ray <><

 

[MDXer]

The best way to handle a DateTime dimension is to create a Dimension table with 1 record per whatever the grain is, if the grain is day then 1 row per day. Use surrogate keys to key to the fact table. Then in your dim you can use the surrogate key as the member key and the date as the Member Name. This allows you to utilize the uniqueness in the dimension as well. if you need I have a date dim script that can help point you in the right way.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[rheilman]

Thanks MDXer!

I'm going back into the view from which and dimension was created and adding the surrogate name field.

Ray <><

"I was going to change my shirt, but I changed my mind instead." - Winnie the Pooh