Hi,

I am using dimensions: Product, Customer, Customer Geogrpahy (this one
is a virtual dimension).

The DDLs are posted below in case they might help....

The problem that I am running into is that when I see the "ALL" Sales or
Zone Level sales, I get the correct results. However, when I go to State
or County level, I get wrong results. Let me explain what I mean:

Suppose the Total Sales in ALL Zones is $ 100. If I view the Sales
broken down at Zone level, the total adds up to $ 100 and also shows the
correct sale figure for each zone.

However when I see the Sales broken down at State Level, the total does
add up to $ 100 BUT the sales for each State is wrong i.e.

State Sale
----- -----
A 30
B 15
C 35
D 20


Now though the above adds up to 100 but in my underlying tables the
actual data is as follows:

State Sale
----- -----
A 30
B 15
C 10
D 45


I was surprised by this and when I checked the sales at the County
Level, it appears that even Counties in which there are no Sales are
being displayed with some sales while actual sales of some Counties
appears to have been "allocated" to Counties where there are no Sales...
This is really confusing and it seems that Analysis Services is
'determined' to show at least something for ALL States and Territories
even if there are no Sales for them.

1) Could this problem be caused due to use of Virtual Dimensions?

2) Is it better to use Snowflake Schema based dimension instead? (
Though these are not shown in the DDLs but I also have the fields for
ZoneCode, StateCode, CountyCode, & CityCode and can link them with my
Geography table/dimension --- If that's what it will take to solve the
problem)

3) Some where in the Wizard steps, there is an option for "Uniqueness".
Exactly what is this option and can this solve my problem.


Any clue? Any possiblity that comes to your mind?

I'll be grateful for your help. Many thanks.


DDLs:
-------

CREATE TABLE [tblCustomers] (
[CustCode] [varchar] (20) ,
[CustName] [varchar] (64),
[CustZone] [char] (10),
[CustState] [varchar] (64),
[CustCounty] [varchar] (64),
)



CREATE TABLE [tblSalesFACT] (
[SaleDate] datetime,
[CustCode] varchar (20),
[ProdCode] varchar (20),
[SaleAmount] DECIMAL (12,2),
[ProdQty] INTEGER
)


CREATE TABLE [tblProducts] (
[ProdCode] [varchar] (20),
[ProdName] [varchar] (50) ,
[ProdFamily] [varchar] (20) ,
)

--
Thanks.

One thing to check is whether the Geography hierarchy, implicit in your
Customer table, is consistent with the Geography table. You could define
Geography as a snowflake dimension to eliminate this possibility. Or you
can define Customer as a view, so that only the leaf level of customer
Geography (CustCounty?) is stored in the table. Then CustState and
CustZone are derived by a join to Geography.


- Deepak

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

Hi Deepak,

Thanks for the reply.

Can you kindly elaborate on what you mean by:



Can you kindly also elaborate a little more on what you are suggesting
below:





I'll be grateful for your help.

Many TIA.

The main issue that I was trying to raise is this: the Customers table
has an implicit Geography hierachy (CustCounty/CustState/CustZone),
which is presumably the basis for your virtual Geography dimension. How
are you ensuring that this hierarchy is identical to your actual
Geography hierarchy, unless you join to actual Geography?

The second question is: how do you know that the county/state
drill-downs are, in fact, incorrect? Do you have another reporting
mechanism, besides the OLAP cube?


- Deepak

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

Hi Deepak,

I don't know if this would answer your question but here's some
background:

The Customers table is based on a view which is joined with the
Geogrpahy table and the contents have been verified with other reports
(we do have other reporting systems in place)..


Regards.

--
Thanks.

Only thing I can think of is to try and isolate where the allocation of
Sales to Geography members is failing:

- Is the Sales by Customer correct for every Customer?
If not, then you have to fix this first.

- Consider a county where the Sales appear incorrect:
who are the Customers with that CustCounty code, and what
is the total of their Sales (fact table vs. cube)? This
should be the same as the cube Sales for that county.


- Deepak

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