官术网_书友最值得收藏!

首頁 >

Business Intelligence with MicroStrategy Cookbook

Building Data Explorer Hierarchies

In every MicroStrategy project, there is a default system hierarchy that is built automatically based on the information we set into the Parent/Children tabs of the Attribute Editor. The default system hierarchy cannot be modified and contains all the attributes in a project.

The hierarchies can be used to drill down and up in reports, for example, from Year to Month to Date; or from Product Category to Product.

In order to present the attributes in clear and understandable groups to the users, additionally, we can create other hierarchies that we will later use in reports and in dynamic selectors; think of it as a different way to show dimensions to the end user.

Getting ready

You need to have completed the previous recipes to continue.

How to do it...

We are creating the most common hierarchy, the time:

  1. Click on the Schema Objects | Hierarchies folder. Then open the Data Explorer folder.
  2. Right-click on the right pane and from the context menu select New | Hierarchy.
  3. In the shopping cart that appears, choose Year, Month, and Date attributes and move them to the right.
  4. By default all the relationship paths are already present in form of arrows from the parent to the child. You can check here if you set the attribute properties correctly.
  5. See the green check mark on Year? This means this is the highest of the three attributes, therefore MicroStrategy automatically selected it as an entry level.
  6. Click on Save and Close, a message warns you that this hierarchy will be used for drilling. Click on OK.
  7. Name it Time and click on Save.
  8. Remember to update the schema and go to the Data Explorer folder.
  9. Here we can see the Time hierarchy, and inside it the entry level: Year.

How it works...

We will see that the hierarchy is useful when we create reports and want to give the user the possibility to aggregate or filter by year, by month, or by date without previously knowing which attribute they are going to use.

There's more...

Entry points are the objects from which the navigation begins inside a hierarchy. You have seen, when browsing the Time hierarchy that only the Year attribute appears at first. To set the Month attribute as another entry point, do this:

  1. Re-open the Time hierarchy by double-clicking on it in Schema Objects | Hierarchies | Data Explorer.
  2. Click on the Month attribute to select it, you will see eight small squares surrounding it.
  3. Now right-click on Month and in the context menu select Set as entry point.
  4. Click on Save and Close, update the schema.
  5. Now go to Data Explorer | Time and press F5 to refresh.
  6. The Month attribute now appears as an entry point.

It may seem useless now, but please wait until Chapter 5, Data Display and Manipulation – Reports.

Sometimes—while the project is in progress—we need to provide a quick and easy documentation about the dimensions. I found that doing a print screen of the Hierarchy Editor is very useful and gives a clear idea of how the users can move inside the data.

Note

You can watch a screencast of this operation at:

Exercise 8

Create the Products hierarchy with the following attributes: Product Category, Product Subcategory, and Product.

Exercise 9

Create the Geography hierarchy with the following attributes: Country, StateProvince, City, and Customer.

Set Country and City as entry points.

Exercise 10

Go to Data Explorer | Geography: how many customers are there in Frankfurt 91480? Double-check the names with this query in sqlcmd:

select LastName from DimCustomer where GeographyKey = 118
go
主站蜘蛛池模板： 赣榆县| 和平区| 凌云县| 桃源县| 东丰县| 嘉义市| 咸阳市| 鸡泽县| 龙井市| 滦平县| 安图县| 四子王旗| 祁东县| 泸西县| SHOW| 兴隆县| 调兵山市| 建阳市| 修文县| 礼泉县| 五指山市| 永城市| 炎陵县| 万载县| 兖州市| 寿光市| 岳西县| 平遥县| 阿合奇县| 彭阳县| 龙南县| 长宁区| 景泰县| 上蔡县| 盖州市| 乌苏市| 五河县| 怀安县| 正阳县| 大姚县| 黄山市|