Tips and Tricks Within Microsoft Analysis Services
- Dimensions with Ragged Hierarchies
- Virtual Cubes
- Dimension Level Security
- Advanced Dimension Properties
- Optimizing Cube Schemas
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Microsoft Analysis Services (MSAS) offers a variety of useful wizards to get you up-to-speed with building dimensions and cubes. In fact, the wizards are very useful and make the life of a cube architect easier. However, there is much functionality that isn’t available through wizards or isn’t immediately apparent when using wizards. In this article, I’ show you a few tricks that will help you make your MSAS dimensions and cubes more powerful.
Dimensions with Ragged Hierarchies
MSAS dimensions consist of one or more levels. Each level shows you business performance as it applies to a subset of the dimension. For example, the geography dimension could have the following levels: continent, country, region, state, and city. This dimension assumes that each city can roll up to a state, each state to a region, each region to a country, and so forth. Each value within a level is called a member; for example, Washington is a member of the state level within the country of USA.
Businesses often have dimensions that have varying number of levels depending on a particular member. Organizational structures within companies are a good example. Let’s suppose that a company is headed by Chief Executive Officer (CEO). Peeon’s like Baya, Jimmy, and Christina report to Vice Presidents (VP), VP’s report to Senior Vice Presidents (SVP), and SVP’s report directly to the chief. One fine day, the CEO decides that one of the VP’s will report directly to her, so your employee dimension looks like this:
|
emp_id |
name |
Title |
VP |
SVP |
|
1 |
baya |
peeon |
tom |
art |
|
2 |
jimmy |
peeon |
kevin |
NULL |
|
3 |
christina |
go-getter |
bob |
fred |
Such dimensions look fine on paper, but it makes the Analysis Services dimension look rather silly because Jimmy has a blank member in the SVP level, as shown in Figure 1.
)
Figure 1 The employee hierarchy looks odd with a blank SVP member.
Furthermore, the report of all sales looks inconsistent (see Figure 2).
)
Figure 2 Report of all sales looks inconsistent with a blank SVP member.
Hierarchies that have varying number of members per each level are referred to as ragged hierarchies. Fortunately, MSAS has a way of handling such hierarchies graciously. If you select the Advanced tab within dimension properties, you’ll see a property called "hide member if". The default value for this property is "never hidden". In addition, you have options of:
"No name" "Parent’s name" "Only child with no name" "Only child with parent’s name"
Let’s modify our dimension table data so both VP and SVP columns have value of "evin" for emp_id of 2. This can be accomplished by running the following SQL statement:
UPDATE employee_reports SET SVP = ’kevin’ WHERE emp_id = 2
Let’s also change the dimension’s "hide member if" property to have the value of "Parent’s name". Figure 3 shows what we see in Analysis Manager after the dimension is processed.
)
Figure 3 The blank SVP member no longer appears in dimension browser.
As you can see, the SVP level is simply ignored for Jimmy because both SVP and its parent (VP) have the same value ("Kevin").
You can use the same property to modify the default behavior of ragged hierarchies if the member you want to ignore has no name, is the only child of its parent member with no name, or the only child with the parent’s name.