Hi and thank you

My Terrritory Budget structure is as follow
Territory ID
Budget amount
Start Date
End Date

Dimension Territory
TerritoryID
TerritoryName

Time Dimension
year,Qtr,Month

Territory fact Table (Time_key,Territory_key,BudgetAmt)
And the lower level of time is Month.

Sales record Information
SalesOrderID
Salesdate
TerritoryID
RegionID
DistrictID
SalesAmount
(the data is by date such as 1/15/2005,etc)

Organization Dimension (RegionID,DistrictID,TerritoryID)
region(5), District(10), Territory(560) number of unique item in each of
the members levels

Sales Fact table(Time_key,Organization_key,SalesAmount)
There are other dimension but they are fine.

My problem is as follow:
I would like to create a virtual cube that I can get the following
information
Region District Territory salesAmt BudgetAmt

of course time will be added to it.
The problem is that since the Budget Cube does have the regionid, and
districtid (a way to put a territory under a region and ditrict), when I
append both cube the data is not correct.
If I try to create a mapping so that I can put the region,district and
Territory into the Budget, the values that I am getting are exploding to
the n.
What am I doin wrong.

As Darren suggested in the other thread, you should hopefully be able to
define the Organization dimension with either TerritoryID, or a
surrogate, as the unique key. Then both Territory and Sales fact tables
would join to the Organization dimension on this single key, which
should eliminate the "explosion" of fact records caused by multiple
joins. The 2 cubes can be combined in a virtual cube with the shared
Organization and Time dimensions.

If you also need to analyze data just by flat Territory (disregarding
the Region/District hierarchy), you can create a virtual dimension like
Territory, based on the leaf level properies of the Organization
dimension (of course, this would be simpler if AS 2005 is an option).


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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