August 5, 2009 at 1:33 am
This is a unusual ask. I have a dimension wuith two levels. Area and location. 10 locations in an area.
The numbers to be displayed in SSAS must matched the official figures which have been rounded. So my cube must show as an example
at are level 50.1 sales
sum of all 10 locations within the area 50.2 sales.
Sales are supplied as a multiple of 1000 sales so 50.2 = 50200. It is this rounding that means that they dont all add up.
Any suggestions gratefully recieved.
Ells:cool:
August 5, 2009 at 8:10 am
Not sure i'm following the problem here :hehe:
Your cube has raw data? So it's truly summing up actual sales, which means in your example, the actual sales made was somewhere in the vicinity of 50200, plus or minus 49 and 50 respectively? Doesn't this then mean that the 'official' data is wrong? I always thought rounding was reasonably standard, and it would be hard for two systems to round a number like 50150 to two different numbers (50200 and 50100 - i've always seen it round up when rounding to the nearest hundred).
Can you provide some more information on exactly is the problem?
Steve.
August 5, 2009 at 8:51 am
The rounding is done like this
Location Sales(£) Sales (£1000)
Location119902
Location229903
Location325302.5
Location411401.1
Location511301.1
Location656875.7
Location756875.7
Location811401.1
Location911401.1
Location1023582.6
----- ----
Total for Area25792 25.9
Total for Area Rounded is 25.8
My figures are available to me at area and location level and I have already checked they do not match.
When people query the report and ask for the sales for the area they need to see 25.8 which is the rounded value of the sales in £ for the whole area.
I only recieve the figures into the data warehouse for the sales in thousands of £s.
Hope this explains it.
At the moment I am wondering if I can treat this as a two level or a single level dimension.
I would put the areas and locations on the bottom level and have all of them relating to the area( so area 1 contains 10 locations and the fake location area1) Then maybe show the total for the area 'area1' as just the value of the location 'area1'.
Sorry really difficult to explain. I hope you can understand the problem now. As I only load in data in £1000s to one decimal place there are bound to be differences between the values of one area and the sum of all locations in that area.
:w00t:
August 6, 2009 at 7:46 am
We are getting a bit closer. To simplify the process I started work with just three facts
Females 3.2
Males 2.1
Persons 5.0
So in my cube I want a dimension Gender.There will be no ALL level. IsAggregatable = FALSE for Gender group level of the dimension. ISAggregatable=TRUe is set at Gender Level.
The three Genders at the bottom level of the dimension are
Females
Males
Persons
The next level up in the dimension is gender group and one member
Persons
So querying the cube we get
Persons5.3
Females 3.2
Males 2.1
Persons 5.0
(The persons at the top id the Gender Group Persons)
Now for the gender attribute I have set hideMemberIf ParentName. This means that when querying the dimension it appears as if Gender is of the form:
Persons
- Male
- Female
So Now I get
Persons5.3
Females 3.2
Males 2.1
Now just need to make the value for Gender Group Persons = Gender Persons value rather than the sum of its components. Custom rollup? Some other way?
Thanks.
Mark
🙂
August 7, 2009 at 4:10 am
Solution found! I used a custom rollup which pointed the value to be the value of the leaf 'person' then I made the leaf person visible=false.
Thanks.
Mark
Attached is a word doc explaining in more detail what I am doing. I have included screen shots from BIDS and Excel to show what I am hoping for. I think I am getting closer.
Ells.
😀
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply