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 values

hey guys,

ive run into an issue and im not quite sure how to fix it.. let me explain

i have a cube, by market and i wish to show sales and freight costs.

The freight Fact table has no relationship to the market dimension as freigth is charged on customer order level, not item level.

I have data showing like the following:

Year

2006

Market

Sales

Freight Amount

Auto OEM

$7,115,315.22

$56,620.73

Auto Repl

$18,517,004.07

$56,620.73

Intercompany

$4,772,910.37

$56,620.73

Ind OEM

$12,039.74

$56,620.73

Ind Repl

$18,679,313.71

$56,620.73

Other

$84,263.19

$56,620.73

Grand Total

$49,180,846.30

$56,620.73

I would only like the freigth amount to show at the Grand Total Level. Is this possible? or, if not, is there a way you can combine two dimensions into 1? ie, combine a Freight Account dimemsion and the Market Dimension so that i can see Markets, then the different Freight Accounts by rows?

Thanks in advance,

Scotty

If you look in the cube structure tab in BIDS, you will see that each measure group has a property called IgnoreUnrelatedDimensions, this defaults to true which means that when you slice a measure by an unrelated measure you see the total amount (which is the behaviour you are seeing). Changing this property to false will mean that the measures will only display when they are either sliced by a related dimension or at the highest level.

It's hard to be sure, but from what you have said I don't think combining the two dimension would work.

DTS to Excel, $0

I am exporting some data from a view to an excel file though a DTS. $0 amounts come as -0, what is the reason for that an dhow can I fix it?

Jim,

When I try a straight export with money data equalling zero

or '$0' as strings, either from SQL Server 2005 or 2000 (using

the Import/Export Data wizard in both cases), I get $0.00

or $0 in the Excel file.

Can you be more specific about what you are seeing? What is the

view definition? Is the problem column a table column declared

as money/smallmoney in SQL Server, an expression, or what? Are

you using a wizard or another method to do the export? When you

select from the view in SQL Server, do you see .0000, or something

else?

You haven't given us much to go on.

Steve Kass

Drew University

www.stevekass.com

JIM.H.@.discussions.microsoft.com wrote:

> I am exporting some data from a view to an excel file though a DTS. $0

> amounts come as -0, what is the reason for that an dhow can I fix it?

>

>

|||

DTS is using the view. When I use the view I do not see -0. But the exported data has -0. If I format the column in excel for 2 decimals, it becomes 0.00 so the negative drops. Maybe DTS considers a long decimal after the point and assumes it is negative.

|||

You haven't really answered some of the relevant questions:

Is the problem column a table column declared as money/smallmoney

in SQL Server, an expression, or what? Are you using a wizard

or another method to do the export?

SK

JIM.H.@.discussions.microsoft.com wrote:

> DTS is using the view. When I use the view I do not see -0. But the

> exported data has -0. If I format the column in excel for 2 decimals, it

> becomes 0.00 so the negative drops. Maybe DTS considers a long decimal

> after the point and assumes it is negative.

>

>

|||Thanks for your help. The column is a float in the table. I used the DTS wizard.|||

Apparently the number is a very small negative number. When such a number

is represented in Excel with only a few decimal places, Excel can't show

its absolute value differently from zero, but Excel could display its

sign correctly, yet doesn't. Excel displays the number as negative for

some formats and not for others. I would consider this a

bug, and you might wish to report it. (I don't know where, but if you

post this in an Excel newsgroup someone will probably tell you.)

I reproduced the fleeting - as follows:

In an Excel cell, type -0.00000001, the format the cell in these ways:

Number (2 decimal places). The result is 0.00 [not negative]

Currency (2 decimal places, with () for negatives. The result is $(0.00) [negative]

Currency (2 decimal places, with - for negative values. The result is $0.00 [not negative]

In particular, the currency marker shouldn't affect whether or not the number is shown as negative.

Steve Kass

Drew University

www.stevekass.com

JIM.H.@.discussions.microsoft.com wrote:

> Thanks for your help. The column is a float in the table. I used the DTS

> wizard.

>

DTS to excel, $0

I am exporting some data from a view to an excel file though a DTS. $0 amounts come as -0, what is the reason for that an dhow can I fix it?

Is the data type of the column (contains $0 values) MONEY? I export MONEY data to Currency (default mapping type) data in Excel with no problem. I'm using Excel2003+SQL2000

|||

data type is float, not money. If I format excel column with 2 decimal I get 0.00 without any problem.