Hi,

I am sort of a newbee and would like to solicite ideas for how to do the
following:

Pertinant info:

Our company has 10 territories organized into 3 regions
The Sales manager set goals (transaction counts) for each territory
There is a Territory Dimension table called "Territory" which is a standard
"snow-flake" type dimension with two levels Region and Territory (in reality
it is parent-child relationship, however, I did not contruct it this way in
AS)

Techincal info;
AS 2000 SP 3a
XMLA service
The primary client is a coldfusion web application -- so I write a lot of
MDX

Goal:
To create a member called 'Goal' that can be displayed with the
'Transactions' Measure when the territory dimension is displayed on either
axis.

--Possiblities--
Goals Fact table
Custom Member (I have not done this yet, I have only worked with calculated
members in the measures dimension)
Calculated measure (with lots of iif logic)

Any ideas would be greatly appreciated.

Joe

At first glance, a separate Goals fact table and cube, combined into a
single virtual cube, would be viable. This approach also allows Goals
data to be at a different time granularity (eg: monthly) than Actuals
(eg:daily).

http://groups.google.com/groups?q=sp...l+cube&hl=en&l
r=&ie=UTF-8&selm=3CD162EB.F21F9F6%40dsslab.com&rnum=3
My question concerns Loading Data to the Fact table at different levels.
e.g

"Actuals" Data loads at the Month level.
"Forecast" Data loads at the Quarter level.

Has anyone come up with a Strategy to handle this issue.
I used a virtual Cube and and the 'LookupCube' Function to handle it
but it is extremely slow!

Alternatively I am thinking of manipulating the Dimension in the DTS
stage to handle my data at different levels.

Any other ideas?

Thx in advance

Kevin Fitzgerald MCP


************************************************** ********************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
Message 2 in thread
From: George Spofford (george (AT) dsslab (DOT) com)
Subject: Re: Loading Data at different Levels


View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2002-05-02 08:58:03 PST


A virtual cube should be very fast, and I would recommend this over
parent-child dimensions in this case.

In one regular cube, you disable the month level so that the forecast
data loads to quarter. In the other regular
cube, you leave months enabled. Joining these in a virtual cube means
you don't need to use the LookupCube() function,
of course. If you were using LookupCube() in a virtual cube, then there
was unnecessary MDX.

When you do this, is it slow to process or slow to query? MOLAP and
HOLAP aggregations will be fast to query; complex
MDX will be slow; hopefully, it's not unnecessarily complex.

--
George Spofford
Microsoft MVP
Chief Architect / OLAP Solution Provider
DSS Lab
http://www.dsslab.com
george (AT) dsslab (DOT) com
...

- Deepak

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!