Correlation matrix with time break

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Analysis Services (2000)

Correlation matrix with time break

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-25 : 08:12:10

Asim Naveed writes "Actually I am trying to make a correlation matrix oF products dimension,
thats easy , but problem arises when I want to see it quarter wise and
the time break for correlation calculation is that quarter's months.
Consider 2 dimensions

TIME
PRODUCT

I made another dimension for product called PRODUCT2

Now I executed the following query to accomplish the below report.

WITH MEMBER [Measures].[CR] AS
'Correlation([TIME].CURRENTMEMBER.CHILDREN,
(Product.currentmember, product2.[all product2], measuers.sales),
(Product2.currentmember, product.[all product], measures.sales))'
SELECT
PRODUCT.CATEGORY.MEMBERS ON COLUMNS,
CROSSJOIN(PRODUCT2.CATEGORY.MEMBERS,
[TIME].[QUARTER].MEMBERS) ON ROWS
FROM SALES
WHERE [MEASURES].[CR]

--------P1----------P2 -------- P3
P1 Q1 1 ---------- .8---------- .5
----Q2 1 ----------.3---------- .2
----Q3 1 ----------.1 ---------.65
----Q4 1 ----------.23 ----------.25

P2 Q1 .8---------- 1 ----------.51
----Q2 .3 ----------1 .----------22
----Q3 .1 ----------1 ----------.75
----Q4 .23 ---------1 .----------85

P3 -Q1 .5 ----------.51---------- 1
----Q2 .2---------- .22 ----------1
----Q3 .65---------- .75 ----------1
----Q4 .25---------- .85---------- 1

The query is executing without error, but the result contains
correlations like 3.45, 2.34, -2.73 etc. Note that the above
result is what I want NOT the result of above query.

According to my knowledge correlations is between -1 and 1. it can
never be less than -1 or greater than 1.

How can I achieve this, I also tried by using TIME2 dimension
and LINKMEMBER but then it gives and error that
"the level name is not unique :month"

I hope you understand the problem, please reply urgently as this
is my assignment in my office and i am stuck with it

Thanks
Asim Naveed"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources