I have two dimensions

1- Geography
------------

Level 1 country
Level 2 Region
Level 3 Area
Level 4 Territory
Level 5 Subterritory


2- Distributors (This dimension has only one level)
---------------
Distributor Name

There are 20 distributors namely distributor1, distributor2,
distributor3, ...... and so on till distributor20.



There is a condition that each region has specific
distributors.
e.g
if there are 20 distributors and 4 regions then

region1 has 4 distributors, region2 has 6, region3 has 2
and region4 has 8 distributors.


When I cross join the set of regions with distributor, and
display the result set, it joins every region with every
distributor. What I want is a join of region and distributor such
that it displays each region and only that region's distributors.
Note that nonempty crossjoin will not solve my problem because there
are regions in which there is no sale done by its distributor(s) and
I still want to display that distributor alongwith its region and
sales=0.


i.e the report should look like this


Regions Distributor sales
-----------------------------------
Region1 Distributor1 23
Distributor2 2200
Distributor3 0
Distributor4 0

Region2 Distributor5 400
Distributor6 9090
Distributor7 898
Distributor8 4333
Distributor9 89893
Distributor10 0

Region3 Distributor11 343
Distributor12 343

Region4 Distributor13 3434
Distributor14 343
Distributor15 343
Distributor16 343
Distributor17 343
Distributor18 0
Distributor19 343
Distributor20 43

Please note that I have already read details on "Dependent dimensions"
and "virtual dimensions" in the help of Analysis
services and have come to the conclusion that both of these do not offer
any solution for my case. I will be grateful for any
help in the above context. Can "name sets" do anything in this context?
Asim Naveed

--
Posted via http://dbforums.com

Why are you using two separate dimensions here? Isn't Distributor just a
level in Geography?

"asim73" <member31195 (AT) dbforums (DOT) com> wrote

I must use distributor as a seperate dimension because I want
to see reports like sales by quarters by distributor etc.

Also the real problem is that region, product and distributors
are related
i.e for each region, there is one or more distributors for one or more
products.

This means we have relation between 3 dimensions.
e.g
product 1, 2 & 3 can be sold by distributor1 & 5 in region1
product 4,6,7 can be sold by distributor3 and distributor5 in
area3 of region1 etc.


I have read the "Dependent Dimensions" topic in Analysis services,
which states that A dependent dimensions is that whose members
are determined by another dimension's members. But it only allows
me to specify that which dimension is dependent on which dimention,
there is no place to specify that member1 of dimension1 is dependent
on member5 of dimension2.



Originally posted by Nigel Pendse


--
Posted via http://dbforums.com

What are you using for a front-end? There are lots of clients that can
display the results of the crossjoin that you wish. If you are using a
client tool that doesn't support this capability, you should consider adding
another dimension to the cube that combines the two. You would still have
your existing ones. Personally, I'd look for a client tool that supports
filtering out empty rows from a crossjoined result set.

Sean


--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.



"asim73" <member31195 (AT) dbforums (DOT) com> wrote

You can add the distributor to the dimension to produce
the effect you want and also then add a virtual dimension
for the Distributor to get the other dimension all by
itself. This will give you what you want in both cases.

Sue

Did you find a solution for your problem ?

From http://www.developmentnow.com/g/112_...imensions-.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

Did you find a solution for your problem ?

From http://www.developmentnow.com/g/112_...imensions-.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com