December 17, 2010 at 10:31 am
Hi all,
I have a cube with following a date dimension having following attributes:
- Key
- Month name and year
- Month name of year
- Quarter number of year
- Year
I have a measure group with date key and an amount in dollars (a relation with the Date dimension is established with date key).
I'm browsing the cube and I have the following scenario:
- Quarter number of year in rows
- Year in columns
- Amount in dollars in data area.
Something like this:
2011 2012
Q1 $720 $900
Q2 $750 $910
Q3 $800 $920
Q4 $830 $930
How can I achieve it? Is there any way to aggregate date dimension data?
What I reach with my setup is the following:
2011 2012
Q1 $720
Q2 $750
Q3 $800
Q4 $830
Q1 $900
Q2 $910
Q3 $920
Q4 $930
But I would like to have quarter numbers once.
Attached is an example of output in which I would like to have quarter numbers once.
Thanks.
January 5, 2011 at 8:22 am
Hi Has anyone done something like that?
Thx
January 5, 2011 at 12:37 pm
How did you set the key of the quarter attribute? Did you define attribute relations for the dimension?
You can add a extra quarter attrubute not directly related to year. then use this attibute in your report.
January 6, 2011 at 3:23 am
You probably have set up the quarter attribute as non-repetitive.
Meaning, the quarters are actually 2011Q1, 2011Q2, 2011Q3, 2011Q4, 2012Q1, 2012Q2, 2012Q3 and 2012Q4.
So they are distinct, just not what you expect.
As de Haas mentioned, you need to create the attribute so that it only contains Q1, Q2, Q3 and Q4.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 6, 2011 at 10:08 am
You need a distinct quarters attribute. I have in my DW both an indistinct quarters and distinct. Months are the same. I have attached the output from one of the cubes crossing the Year with the Distinct Quarter attribute.
January 10, 2011 at 2:49 am
Hi all,
thanks for your response. I easily fixed my problem as you described and all worked as I expected.
🙂
August 5, 2011 at 7:57 am
hey,
I have created a Date Dimension in my cube. I am using the same Date table that has been used in the MS AdventureWorks sample project.
I have following dimensions in my Date dimension:
Date Key
English Month Name
Fiscal Quarter
Fiscal Year
Full Date Alternate Key
Month Number of Year
When I drop a measure and Monthname in the work area Monthname gets repeated as many times as dates are there in that respective month.
Please refer the attachment.
Please guide me to get distinct month names in the dimension.
Note: Hierarchy is working fine. Problem is when I want to aggregate measure at month level.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply