I a dimension with the following Hierarchy
Region (7)
District (20)
territories (568)

the problem is that my budget fact table is base on Territories
when I create the cube with the territories only I get the correct data
between the various dimension that are also attach to the facts
but when I add the region and the district , the numbers get duplicated
hundres of time.
What am I doing wrong
What should I do
Thank you
Tomas

Hi Tomas,

This sounda like a schema problem - maybe there is an incorrect join
between the fact table and the dimension table containing
Region/Ditrict/Territory? Does this dimension table have a unique key
(like Territory), and what field joins to the fact table?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

The Budget fact for the rep have only a Territory id that you can, also it is
on a monthly too.
In Sales fact table I have territory as a comformed dimension that I use
between the two fact tables, but it also contains a dimension for
(region,district).
The problem is that I do have a hierarchy for region,district and territory,
but only at the Sales fact. When I try to do it for the Budget, everything
break because of that.
I tried to create a fact for Budget with a unique key for region,district
and territory, but as you know , that will create extract records, therefore
making the budget table a lot bigger than it should be, therefore the budget
will be way off.

thank you for any help

I am using a Lookupcube , but I keep hearing that it is bad when come to
performance. AS 2005 is out of the question right now.

Thank you
Tomas

"Deepak Puri" wrote:

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.




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

Unless you move territories between districts/regions, you should not
need to use the RegionId, DistrictID in the sales fact table, even then
there are techniques using surrogate keys for handling what is known as
slowly changing dimensions. You should be able to just link to your
organization dimension using the territoryId for both budget and sales.

I suspect that another issue you might be having is that your date
granularity is different between budget and sales. Budget is per month
and sales appears to be daily, so if you link these 2 directly your
budget will be multiplied by the number of days for which you have
sales.

You need to create the budget cube so that the date is linked to the
month level of the time dimension. (you could even disable the day
level). Then join sales and budget in a virtual cube.

HTH

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

In article <ey28zh01FHA.1028 (AT) TK2MSFTNGP12 (DOT) phx.gbl>,
sanpoco (AT) sbcglobal (DOT) net says...