Q. Is it possible to load ratios at each level of a dimension and avoid cube aggregations? In this case, the components of the ratios are unknown and cannot be decomposed, but the ratios at each level are known.
A. Take a look at Richard Tkachuk’s article at http://sqlserveranalysisservices.com/OLAPPapers/Loading%20Aggregate%20Data%20in%20AS2005v2.htm. The first part of this article explains the process for parent-child hierarchies. Look for the section titled “Without Parent-Child Hierarchies” for an explanation of how to solve this problem for a user hierarchy. This article is focused entirely on the dimension design, however. Let’s consider how you combine this technique with fact table and cube design. I’ll extend Richard’s example by using a similar Geography dimension.
Here are the tables used to create the dimension:
| CityKey | StateKey | CityName | StateKey | CountryKey | StateName | |
| 1 | 1 | Seattle | 1 | 1 | WA | |
| 2 | 1 | Tacoma | 2 | 1 | OR | |
| 3 | 2 | Portland | 3 | 1 | CA | |
| 4 | 2 | Salem | 4 | 1 | NV | |
| 5 | 3 | Los Angeles | 5 | 1 | USA | |
| 6 | 3 | San Francisco | ||||
| 7 | 4 | Las Vegas | ||||
| 8 | 4 | Reno | ||||
| 9 | 1 | WA | ||||
| 10 | 2 | OR | ||||
| 11 | 3 | CA | ||||
| 12 | 4 | NV | ||||
| 13 | 5 | USA | ||||
| 14 | 5 | All |
| CountryKey | Country |
| 1 | USA |
| 2 | Canada |
After creating the dimension by using these tables, I opened the dimension designer to create a user hierarchy: City, State, Country (bottom to top). Then, on the Dimension Structure tab, I changed the HideMemberIf property for the city and state levels in the user hierarchy to ParentName to hide WA, OR, and CA from the City level and USA from the State level. The dimension now looks like this after deploying the project and processing the dimension:
The fact table uses only keys in the City table, like this:
|
CityKey |
Ratio |
|
1 |
0.200 |
|
2 |
0.320 |
|
3 |
0.112 |
|
4 |
0.708 |
|
5 |
0.357 |
|
6 |
0.405 |
|
7 |
0.811 |
|
8 |
0.057 |
|
9 |
0.260 |
|
10 |
0.410 |
|
11 |
0.381 |
|
12 |
0.155 |
|
13 |
0.392 |
|
14 |
0.392 |
After creating a cube from a measure group based on this fact table, I opened the Calculation tab of the cube designer, clicked the Script View button on the designer toolbar, and added the following code below the CALCULATE; command:
this = STRTOMEMBER(“[Geography].[Geography].[City].[“ + [Geography].[Geography].CurrentMember.Name + “]”);
I deployed the project and browsed the cube as shown below. Note the values at the state, country, and All level do not aggregate the value on the city level, but use the values provided in the fact table.
