MS SQL Server OLAP - Merge Times in a virtual cube?

Author

Merge Times in a virtual cube?

Cindy Lee

2005-09-22, 8:24 pm

I need to get a MDX result set with a measure from CubeA (Acount) and a
measure from CubeB (Bcount). I want to get these measures with the same
Zone Id and same Time dimension. I also want the time dimension to be in
the rows axis.
It seems the best way to do this is to make a virtual cube (VCTest).
I can't seem to merge dimensions in a virtual cube, so I have to call 2
Zones and Times in my virtual cube.

I have a query below that works well without the time, but I don't get any
results when I have both times.
If I add time to the Members I make, I can't see the time the rows. (I'm
using this for a reporting services graph)

Is there anyway to merge times in a virtual cube, or merge it in my query?

This is my query that doesn't work
with
MEMBER [Measures].[Atest] AS
'([Measures].[Acount],[ZoneA].[1] ) '

MEMBER [Measures].[Btest] AS
'([Measures].[Bcount],[ZoneB].[1]) '
select
{[Measures].[Atest], [Measures].[Btest]} on columns,
{([TimeA].[2005],[TimeB].[2005])} on rows
from [VCTest]

I want
Atest | Btest
2005 2
2005 3

or Atest | Btest
2005 2 3

if possible

Deepak Puri

2005-09-23, 8:24 pm

It sounds like both Zone and Time should be shared dimensions in the
database, so that the same version of each can be applied to the cubes
and virtual cube.

But maybe the query will work with ValidMeasure():
[color=darkred]
with
MEMBER [Measures].[Atest] AS
'ValidMeasure(([Measures].[Acount],[ZoneA].[1]))'

MEMBER [Measures].[Btest] AS
'ValidMeasure(([([Measures].[Bcount],[ZoneB].[1]))'
select
{[Measures].[Atest], [Measures].[Btest]} on columns,
{([TimeA].[2005],[TimeB].[2005])} on rows
from & #91;VCTest][color=da
rkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Cindy Lee

2005-09-26, 8:24 pm

Thanks, validMeasure works.

Shared dimensions don't work well in our situation because they take more
time to process and have caused things to crash.

"Deepak Puri" < deepak_puri@progress
ive.com> wrote in message
news:OEZXb%23HwFHA.3236@TK2MSFTNGP14.phx.gbl...
> It sounds like both Zone and Time should be shared dimensions in the
> database, so that the same version of each can be applied to the cubes
> and virtual cube.
>
> But maybe the query will work with ValidMeasure():
>
> with
> MEMBER [Measures].[Atest] AS
> 'ValidMeasure(([Measures].[Acount],[ZoneA].[1]))'
>
> MEMBER [Measures].[Btest] AS
> 'ValidMeasure(([([Measures].[Bcount],[ZoneB].[1]))'
> select
> {[Measures].[Atest], [Measures].[Btest]} on columns,
> {([TimeA].[2005],[TimeB].[2005])} on rows
> from [VCTest]
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.droptable.com ***

Cindy Lee

2005-09-26, 8:24 pm

It works except the date part of my function.
I use LastPeriods:
{ LastPeriods(12, [Time].[2005].&[1]) , LastPeriods(12,
[Time].[2005].&[1]) }
This doesn't work
crossjoin(LastPeriod
s(12, [Time].[2005].&[1]) , LastPeriods(12,
[Time].[2005].&[1]) )
doesn't work either because I get January mixed with January-Dec.

Is there another function I can use?

"Cindy Lee" <cindylee@hotmail.com> wrote in message
news:Oxoqz4uwFHA.2232@TK2MSFTNGP11.phx.gbl...
> Thanks, validMeasure works.
>
> Shared dimensions don't work well in our situation because they take more
> time to process and have caused things to crash.
>
> "Deepak Puri" < deepak_puri@progress
ive.com> wrote in message
> news:OEZXb%23HwFHA.3236@TK2MSFTNGP14.phx.gbl...
>
>

Darren Gosbell

2005-09-27, 3:24 am

>
> Shared dimensions don't work well in our situation because they take more
> time to process and have caused things to crash.
>

This sounds strange. I always use Shared dimensions over private ones if
I can as they take less time to process because you only have to process
them once for the whole database and they use less memory. AS will hold
both copies of a dimension in RAM if you have 2 private dimensions.

You get the added advantage that virtual cubes work better with shared
dimensions.

What did you mean by the cause things to crash? I have some shared
dimensions in the range of 340,000 members with no problems.

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

Cindy Lee

2005-09-27, 1:23 pm

When cubes are in a batch(even if i process shared dimensions first) Every
cube processing them again so the processing time doubles.
It also used to crash another cubes when i was trying to process them in
parrallel

"Darren Gosbell" <xxx@xxx.com> wrote in message
news:MPG. 1da35da6e1c34d629896
cd@news.microsoft.com...
>
more[color=darkred]
>
> This sounds strange. I always use Shared dimensions over private ones if
> I can as they take less time to process because you only have to process
> them once for the whole database and they use less memory. AS will hold
> both copies of a dimension in RAM if you have 2 private dimensions.
>
> You get the added advantage that virtual cubes work better with shared
> dimensions.
>
> What did you mean by the cause things to crash? I have some shared
> dimensions in the range of 340,000 members with no problems.
>
> --
> Regards
> Darren Gosbell [MCSD]
> < dgosbell_at_yahoo_do
t_com>
> Blog: http://www.geekswithblogs.net/darrengosbell

Darren Gosbell

2005-09-27, 8:24 pm

> When cubes are in a batch(even if i process shared dimensions first)
> Every cube processing them again so the processing time doubles.

Shouldn't the time taken to process 1 shared dimension twice be
equivalent to processing the same information as 2 private dimensions?

> It also used to crash another cubes when i was trying to process them in
> parrallel

Yes, Parallel processing does have issues in AS2k. And you are right
that private dimensions are easier to manage in this regard. You
definitely have to process all the shared dimensions first or you will
see crashes as more than one cube tries to process the dimensions.

However in terms of memory foot print and use in virtual cubes, shared
dimensions are better in my opinion.

I had a 20Gb AS2k database with about 6 cubes and we about 12 shared
dimensions, the largest of which had 345,000 members at the leaf level
(we did use 2 private dimensions which gave us subsets of our big
dimension). I built a modified version of the parallel process tool that
comes in the SQL Server Resource Kit so that it automatically processed
all the shared dimensions on one "thread" and then processed all the
partitions on the cubes on multiple threads.

I mainly wanted to point out that shared dimensions are preferable over
private one is most circumstances, although in your circumstances I do
agree that with parallel processing they are difficult to manage.

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

Sponsored Links