Duplicate Values on Top Level of Dimension

Hey there community,

I have a problem, and i am lost as to how i can fix it.. I would be over joyed if someone can give me a hand in identiffy it.. I will try to explain below::

I have a cube for sales, that displays sales by a Market heiracy of Market->Division->Family->Item. When i drill down, sales numbers look fine..

I added in a budget file, which only specifys budgets down the the Division Level.. I have added the Budget Figure as a Measure Group. In the dimension usage tab, i specify the Division as the Granuality for the Budget Measure Group on the Market Dimension..

	Measure Groups
Dimensions	Standard Measure	Budget Measures	Freight Amount
Date	Date	Period	Date
Sales Person	Sales Person		Sales Person
Order Type	Order Type
Customer	Customer	Country	Customer ID
Market	Item	Division

When i build and deploy my cube, and i drag in the Market Heiracy, i add the sales fugures and the budget amounts. The top level, should split down on the Budget Figure, but i get the same value repeated all the way through like below:

Market	Sales Amount	Budget Amount
aaaaa	527434.08	3819283
bbbbb	1605726.50999999	3819283
cccccc	640.06	3819283
ddddd	1488549.15	3819283
eeeee	9107.59	3819283
Grand Total	3631457.39	3819283

when i Drill Down on the Market to the Division Level, the splits are correct, but the totals are all the same all the way down.. The should be the total of that market...

Market	Division	Sales Amount	Budget Amount
aaaaa	GP	24100.27	13244
	PT	503333.81	635552
	Total	527434.08	3819283
bbbbb	GP	333209.5	335234
	Other	167680.46	127426
	PT	1104836.55000001	1122647
	Total	1605726.50999999	3819283
cccccc	PT	640.06	0
	Total	640.06	3819283
ddddd	GP	790620.499999999	794611
	Other	11206.16	14831
	PT	686722.49	769930
	Total	1488549.15	3819283
eeeee	Other	9107.59	5808
	Total	9107.59	3819283
Grand Total		3631457.39	3819283

I would like the totals to be the total budget for each market.. I am stuck, i have tried some different things, but this is as far as can get..

If i choose the Market to be the Granuality for the Budget Measure Group, then i get the reverse of this.. The market splits are correct, but when i drill down to the Division Level, the values repeat..

Please can someone give me some ideas of what to try!

I can provide more data if need be.

Many thanks in advance!

Thanks Scott Lancaster

I reckon you don't have relationship defined between Market and Division. If you will make Market a member property of Division - everything should become fine (and keep Division as granularity for Budget measure group).|||

Thanks Mosha..

Could you please explain how i do this? Im pretty new to SSAS and have taught myself most of the way

Again.. very very much appriciated..

Thanks

|||In the dimension editor, drag Market attribute and drop it into Division attribute. This will create the needed relationship. If still in doubt, read documentation on the subject of "related attributes" or "member properties".|||

Thanks Mosha!!!!

your are a champion.. I have done this and it has all worked perfectly...

Again.. thanks mate..

Scotty

Duplicate Dimension Members

Hi everyone,

In AS 2000, some of the dimensions I was using had both the key & the name of the dimension as the same text field (dimTable.name). This grouped duplicate dimension members together. Browsing the dimension only showed unique members.

In SSAS, my understanding is you need a key attribute that links to the fact table, or another reference dimension. Since the dimension I was using had an intermediate key on another dimension table, I created a key in the dimension, and used a reference dimension to link indirectly to the fact table to another dimension.

Now I'm getting duplicate dimension members, since the keys are unique in the dimension table. Is there anyway I can consolidate these?

I'll try and diagram for a better explanation.

status dimension table --> dimension table --> fact table

status dimension table has key, name, foreignkey fields that links to a dimension. There can be many statuses for the dimension, based on the foreign key. There are duplicate statuses.

Any help is much appreciated!

...perhaps it's easier to understand if you post some sample data for your scenario... I get a little confused what's duplicate and what's not... Maybe you're talking about a n:m dimension...|||

It seems to be happening not just with the dimension with 2 reference tables, but a standard 1-M dimension-fact relationship.

1 example.

DIM table.

STAT_ID - STAT_NAME

1 Andrew
2 Thomas
3 Thomas
4 Andrew
5 Andrew

Fact Table

STAT_ID
1
1
3
4

AS 2000 Dimension

Key - STAT_NAME
Name - STAT_NAME

Relationship in Cube - STAT_ID - STAT_ID

The result is 1 name in dimensino but keys are related.

AS 2005 Dimension
Key - Stat_ID
Name - Stat Name

A new attribute is created for Stat_ID
This attribute is used in cube Dimensions tab.
The tables are linked in DSV to STAT_ID

The result is duplicate names, due to different keys.

One suggestion is to create a Named query & join on the names in the DSV. I don't want to do that for every dimension we have KEY & Name with the same field in AS 2000.

If I change the Key in the dimension back to STAT_NAME, I can no longer link the granularity settings in the cube.

|||

Andrew,

what's the reason for the key in the dimension table not being unique? Perhaps you should setup some ETL to make this "clean"...

|||

I was thinking about doing that, however I didn't want to change the 2000 data model. Can I do that in the DSV, by just creating a derived query on the name column?

The reason they're not unique is because the data isn't totally denormalized. The actual table looks more like...

key -- status name -- child name

where status name is not unique, but the child is.

So do you think we should split group name off into it's own table, or is there a way to set a property on the dimension so that it will ignore duplicates? It seemed to work in AS 2000.

thanks for all your help,

Andrew

Dummy dimension for passing parameters

Hi,

I'm just having a little problem... I have a calculation, which is a non-trivial formula. This needs input from some dimensions (no problem with that...) and some information, which is independant from the data itself. The idea is to have something like a dimension but which is not connected to the data itself. You can't slice anything using it, it's just used to provide some input for the formula. Just imagine you have some amounts and you want to display the interests for them. The interests are nothing from the cube, they just are needed as input for the formula to calculate the interests. In the cube you only have the amount. Sure, you can add different measures for each interest rate you need, but this might be quite a pain...

Any idea?

Thomas,

You might be able to get what you want by using the same method as was outlined in an article on building a dimension for "cell annotations". Here is the link:

http://www.sqlserveranalysisservices.com/OLAPPapers/Cell%20Annotations%20in%20AS2005v1.htm

The idea is that you have a dimension table that has a compound key made up of the key values that identify a cell in the cube and then the additional attributes can be comments or a formula or whatever you want. You can then retrieve information from the dimension using the script as outlined in the article. The annotations dimension does not have to be related to any measure group.

HTH,

- Steve

|||

Hi Thomas,

AS2005 supports dimensions which aren't connected to any measure group in your cube - just go to the Dimensions tab in the Cube Editor, click on the box at the intersection of the dimension and the measure group, and set the 'Relationship Type' property in the dropdown at the top of the dialog to 'No Relationship'. I've used this method several times to include dimensions in cubes which don't affect any of the regular measures but which are used in calculations to control how they behave.

Regards,

Chris

|||

Chris,

thanks, works perfectly... It's just too easy to find out ;-)

Hope you had a good trip back from PASS... Your session helped me very much to do the custom rollups I asked you about... Seams to work, but some crasy things going on with the scopes... But that's something for later...