Greetings All

I have a question regarding how to show 2 dimensions on a cube that
have different granularities in SQL 2k5AS

Here is my setup
Fact

id snadi datetimeid CD DB Net Type
1 1 1 5 6 -1 A
2 2 1 55 66 -11 B
etc.

the granularity is as follows: the fact table will not have any
duplicates based on type,snadi and datatimeid (in effect PK's)

The measures are based on the type field, i.e. all the a in the above
table make up one set of measures, all the b's and so on.


Dimensions - DateTime links up on datatimeid and is a normal time
dimension (taken from what Kimbal recommends).

e.g.
datetimeid year month, date............
1 2000 1 2004 1/1/2005............
2 2000 2 2004 3/1/2005............

The catch is that all A measures need to be aggregated in a
year-quarter-month way and all B measures need to be aggregated using
year-quarter-month-date way. Hence, 2 different granularities.

I have gone thru the following paper
http://blogs.msdn.com/bi_systems/articles/164185.aspx but still do not
have a good and elegant solutions. Any suggestions would be
appreciated.

Regards

PDI

AS 2005 supports the concept of mult-fact tables.

You can see the case from Adventure Works sample under "C:\Program
Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services
Project\Enterprise"

Go to the Dimension usage of cube designer and verify the setting of Date
dimension of "Sales Target" measure group.

Ohjoo Kwon


"mma" <mma (AT) nospam (DOT) nospan> wrote

Just to expand a bit on the previous response.

You would be better off splitting the A and B records into two separate
tables. These would become to measure groups in an AS2k5 cube and you
can link one to the date dimension based on month and the other based on
days.

HTH

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <u5WjTAx6FHA.736 (AT) TK2MSFTNGP09 (DOT) phx.gbl>, ojkwon (AT) olap (DOT) co.kr
says...

Hi

Thanks for the response. I thought of your suggestion. A question
however, if you do that, how would you go about comparing A and B side
by side in your cube? Basically, would there be any way of combining
the 2 date dimensions into one so that you could drilldown all the way
to the day level (I assume the dim that goes to the month level would
not show)?

pdi


On Thu, 17 Nov 2005 16:57:40 +1100, Darren Gosbell
<jam (AT) newsgroups (DOT) nospam> wrote:

Sorry, I probably was not entirely clear. As far as I can tell, I think
you will only need 1 shared date dimension. You would then link both
fact tables to this one date dimension. That way on any given date you
could see that you would have x of M1 and y of M2.

And you are correct that if you drill down to the day level, the monthly
measure would appear blank.

If you wanted to get fancy you could come up with a calculated measure
that would show an allocated amount at the day level if that was
relevant.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <bsion1lp4iqr5qtqgb2k8bgst7s8k6a6sn (AT) 4ax (DOT) com>,
mma (AT) nospam (DOT) nospan says...