March 27, 2012 at 2:48 pm
Hi,
I want to create a MAT quarter based on the below data and use it as an attribute in cube.
I want to handle it in database instead of handling it in mdx
PeriodKey Period
1 QTR_03_2005
2 QTR_03_2006
3 QTR_03_2007
4 QTR_03_2008
5 QTR_03_2009
6 QTR_03_2010
7 QTR_03_2011
8 QTR_06_2004
9 QTR_06_2005
10 QTR_06_2006
11 QTR_06_2007
12 QTR_06_2008
13 QTR_06_2009
14 QTR_06_2010
15 QTR_06_2011
16 QTR_09_2004
17 QTR_09_2005
18 QTR_09_2006
19 QTR_09_2007
20 QTR_09_2008
21 QTR_09_2009
22 QTR_09_2010
23 QTR_09_2011
24 QTR_12_2004
25 QTR_12_2005
26 QTR_12_2006
27 QTR_12_2007
28 QTR_12_2008
29 QTR_12_2009
30 QTR_12_2010
March 27, 2012 at 2:55 pm
Great! Good for you! 🙂
I assume there was question you wanted to ask, or you were just letting us know? 😀
Jared
CE - Microsoft
March 27, 2012 at 2:57 pm
I require help solution to handle it at database level thats the reason i have posted it
March 27, 2012 at 2:58 pm
What is MAT ? can you explain what your desired result it?
March 27, 2012 at 3:01 pm
Moving Annual Total. I want to roll up the quarters to Moving Annual Total
March 27, 2012 at 3:01 pm
jothibhatt_s (3/27/2012)
I require help solution to handle it at database level thats the reason i have posted it
Handle what? You want that data in a table? We need a lot more information here...
Jared
CE - Microsoft
March 27, 2012 at 3:08 pm
jothibhatt_s (3/27/2012)
Moving Annual Total. I want to roll up the quarters to Moving Annual Total
There are multiple ways.. We need to see some data and ur table structure to effectively guide you. Please read here on how to format ur data so that we can readily consume it: http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 27, 2012 at 3:24 pm
I have a cube and I want to create a Moving Annaul Total column based on the Period column(above). This column is then expected to show up in the same way as a regular metric would as actual column in dimension table
just like this sample
PeriodKey QuarterCode QuarterDescQtrNameMATCode MATDescMATSort
120093Qtr3 2009Qtr3MAT03MAT 07/091
220092Qtr2 2009Qtr2MAT03MAT 07/091
320092Qtr2 2009Qtr2MAT03MAT 07/091
420092Qtr2 2009Qtr2MAT03MAT 07/091
520091Qtr1 2009Qtr1MAT03MAT 07/091
620091Qtr1 2009Qtr1MAT03MAT 07/091
720091Qtr1 2009Qtr1MAT03MAT 07/091
March 27, 2012 at 3:28 pm
You have shown us the sample data, but we need it need it in readily consumable format.. Please read the article i provided in my last post and give us readily consumable data and desired results!
March 28, 2012 at 7:24 am
jothibhatt_s (3/27/2012)
I want to create a MAT quarter based on the below data and use it as an attribute in cube.I want to handle it in database instead of handling it in mdx
WHY? You already have a cube that was specifically designed to do On Line Analytical Processing (OLAP ). These kind of calculations are easy and fast in OLAP. Why would you want to do the calculations in your database where these calculations are hard and slow?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 28, 2012 at 7:33 pm
Found your problem interesting so I decide to convert your posted data to something consumable and came up with this.
DECLARE @d TABLE
(PeriodKey INT, Period VARCHAR(20), Amount MONEY)
INSERT INTO @d
SELECT 1 AS PeriodKey, 'QTR_03_2005' AS Period, 1 AS Amount
UNION ALL SELECT 2, 'QTR_03_2006', 2
UNION ALL SELECT 3 , 'QTR_03_2007',3
UNION ALL SELECT 4 , 'QTR_03_2008',4
UNION ALL SELECT 5 , 'QTR_03_2009',5
UNION ALL SELECT 6 , 'QTR_03_2010',6
UNION ALL SELECT 7 , 'QTR_03_2011',7
UNION ALL SELECT 8 , 'QTR_06_2004',8
UNION ALL SELECT 9 , 'QTR_06_2005',9
UNION ALL SELECT 10 , 'QTR_06_2006',10
UNION ALL SELECT 11 , 'QTR_06_2007',11
UNION ALL SELECT 12 , 'QTR_06_2008',12
UNION ALL SELECT 13 , 'QTR_06_2009',13
UNION ALL SELECT 14 , 'QTR_06_2010',14
UNION ALL SELECT 15 , 'QTR_06_2011',15
UNION ALL SELECT 16 , 'QTR_09_2004',16
UNION ALL SELECT 17 , 'QTR_09_2005',17
UNION ALL SELECT 18 , 'QTR_09_2006',18
UNION ALL SELECT 19 , 'QTR_09_2007',19
UNION ALL SELECT 20 , 'QTR_09_2008',20
UNION ALL SELECT 21 , 'QTR_09_2009',21
UNION ALL SELECT 22 , 'QTR_09_2010',22
UNION ALL SELECT 23 , 'QTR_09_2011',23
UNION ALL SELECT 24 , 'QTR_12_2004',24
UNION ALL SELECT 25 , 'QTR_12_2005',25
UNION ALL SELECT 26 , 'QTR_12_2006',26
UNION ALL SELECT 27 , 'QTR_12_2007',27
UNION ALL SELECT 28 , 'QTR_12_2008',28
UNION ALL SELECT 29 , 'QTR_12_2009',29
UNION ALL SELECT 30 , 'QTR_12_2010',30
;WITH cteReformat AS (
SELECT PeriodKey
,SUBSTRING(Period, 8,4) + SUBSTRING(Period,5,2) As YrQtr
,Amount
FROM @d)
SELECT PeriodKey, YrQtr, Amount
,(SELECT SUM(Amount)
FROM (
SELECT TOP 4 Amount
FROM cteReformat c2
WHERE c2.YrQtr <= c1.YrQtr
ORDER BY YrQtr DESC
) x
) AS MAT
FROM cteReformat c1
ORDER BY YrQtr
Note that in order for it to work, you must be 100% sure there is one and only one record for every quarter. If any are missing or duplicated the solution will not work.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 28, 2012 at 8:15 pm
This rather curious solution returns the same results set (it is even sorted on YrQtr without using ORDER BY) and yields a slightly improved execution plan and faster elapsed time. I'd be curious what happens when Jeff Moden runs it through his 1,000,000 row table. 😀
;WITH cteReformat AS (
SELECT PeriodKey
,SUBSTRING(Period, 8,4) + SUBSTRING(Period,5,2) As YrQtr
,Amount
,ROW_NUMBER() OVER (ORDER BY SUBSTRING(Period, 8,4), SUBSTRING(Period,5,2)) AS RowNo
FROM @d)
,cteCalc AS (
SELECT MAX(CASE WHEN n = 0 THEN PeriodKey ELSE NULL END) As PeriodKey
,MAX(YrQtr) AS YrQtr
,MAX(CASE WHEN n = 0 THEN Amount ELSE NULL END) As Amount
,SUM(Amount) AS MAT
FROM cteReformat
CROSS APPLY (SELECT 0 AS n UNION ALL SELECT -1 UNION ALL SELECT -2 UNION ALL SELECT -3) x
GROUP BY RowNo-n)
SELECT PeriodKey, YrQtr, Amount, MAT
FROM cteCalc
WHERE PeriodKey IS NOT NULL
Of couse, if you eliminate the ORDER BY in the first solution it ends up winning the race. This solution is subject to the same constraints posted for my earlier one.
As to how it works, I'll leave it to the interested reader to attempt to decompose it. Suffice it to say that I like to ingrain the idea of "out-of-the-box" thinking to my company's development team and this would be a case of that.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply