Search

Custom Search

Wednesday, June 3, 2009

What is Scope of Analysis?

Scope of Analysis is one of the most powerful features in business objects. Now you can easily build your custom hierarchies and use them in your report to navigate through data. To make it easy I will consider a small example then go step by step starting from building the universe and end with how to use the drill feature to navigate through data.

Example:

We have one fact table that contains daily sales and number of customers against branches.

Fact Table:

As of Date

Branch CD

Number of Customer

Total Sale

30-Nov-2008

101

10

4245

30-Nov-2008

102

30

543

30-Nov-2008

103

20

6434

30-Nov-2008

104

40

6543

30-Nov-2008

105

28

3264

30-Nov-2008

106

36

657

30-Nov-2008

107

48

677

30-Nov-2008

108

48

675

31-Dec-2008

101

45

546

31-Dec-2008

102

54

765

31-Dec-2008

103

63

658

31-Dec-2008

104

23

768

31-Dec-2008

105

98

876

31-Dec-2008

106

67

786

31-Dec-2008

107

34

453

31-Dec-2008

108

21

564

31-Jan-2009

101

23

546

31-Jan-2009

102

43

545

31-Jan-2009

103

67

654

31-Jan-2009

104

32

654

31-Jan-2009

105

45

675

31-Jan-2009

106

21

765

31-Jan-2009

107

45

675

31-Jan-2009

108

67

546

28-Feb-2009

101

64

657

28-Feb-2009

102

21

865

28-Feb-2009

103

36

324

28-Feb-2009

104

73

143

28-Feb-2009

105

15

432

28-Feb-2009

106

24

324

28-Feb-2009

107

53

324

28-Feb-2009

108

21

532

31-Mar-2009

101

64

321

31-Mar-2009

102

54

432

31-Mar-2009

103

23

543

31-Mar-2009

104

25

543

31-Mar-2009

105

53

546

31-Mar-2009

106

21

564

31-Mar-2009

107

68

544

31-Mar-2009

108

54

654

30-Apr-2009

101

34

546

30-Apr-2009

102

43

654

30-Apr-2009

103

21

567

30-Apr-2009

104

54

764

30-Apr-2009

105

21

675

30-Apr-2009

106

65

765

30-Apr-2009

107

32

765

30-Apr-2009

108

11

745

31-May-2009

101

33

546

31-May-2009

102

45

547

31-May-2009

103

56

564

31-May-2009

104

78

544

31-May-2009

105

98

654

31-May-2009

106

76

546

31-May-2009

107

65

654

31-May-2009

108

54

567


 

Branch hierarchy Table:

Region

Area

Branch CD

Branch

Central

First Area Riyadh

101

Branch 101

Central

First Area Riyadh

102

Branch 102

Central

Second Area Riyadh

103

Branch 103

Central

Second Area Riyadh

104

Branch 104

West

Jeddah

105

Branch 105

West

Jeddah

106

Branch 106

West

Mekkah

107

Branch 107

West

Mekkah

108

Branch 108


 

Time hierarchy Table:

As of Date

Month

Quarter

Year

30-Nov-2008

Nov

Q4

2008

31-Dec-2008

Dec

Q4

2008

31-Jan-2009

Jan

Q1

2009

28-Feb-2009

Feb

Q1

2009

31-Mar-2009

Mar

Q1

2009

30-Apr-2009

Apr

Q2

2009

31-May-2009

May

Q2

2009


 


 

Build The Universe:

To build the universe just adds the Fact Table and the 2 hierarchy tables. Make the proper joins and create 3 main classes:


 

Tables:

  • Fact Table.
  • Branch Hierarchy table.
  • Time hierarchy table.

Classes (Universe window should be like this):

Fact Table (class)

----------------------

| -----> As of Date (Dimension)

| -----> Branch Code (Dimension)

| -----> Number of Customers (Measure) [Sum]

| -----> Total Sales (Measure) [Sum]


 

Time Hierarchy (Class)

----------------------

| -----> As of Date (Dimension)

| -----> Month (Dimension)

| -----> Quarter (Dimension)

| -----> Year (Dimension)


 

Branch Hierarchy:

-----------------------

| ----->Region (dimension)

| ----->Area (dimension)

| ----->Branch CD (dimension)

| ----->Branch (dimension)

Joins:

First Table

Second Table

Join criteria

Fact Table

Branch Hierarchy table

Branch CD = Branch CD

Fact Table

Time hierarchy table

As of Date = As of Date


 

Hierarchies:

By default in business objects it consider every dimension created as a standalone hierarchy. By for some cases as our example you want to define a custom hierarchy like time, geographical information and customer segmentation…etc. in our example we need to build 2 custom hierarchies.

Click on hierarchies button from the editing tool bar or go Tools --> Hierarchies

Select the custom hierarchy option and then start building your hierarchy.

You can just add the Time Hierarchy class and Branch Hierarchy class from the left ban to the right ban. Or you can click new button from the right ban. Then type you hierarchy name. after that start adding dimensions from the left ban to your hierarchy tree. When you finish you can start order your hierarchy level by clicking move up and down buttons. Your hierarchy tree should be like this at the end:

Branch Hierarchy:

-----------------------

| --------- > Region

| --------- >Area

| --------- >Branch

Time Hierarchy:

----------------------

| --------- > As of Date

| --------- > Month

| --------- >Quarter

| --------- >Year

Note That if you even store all your data as detailed in one big table you will be able to build your hierarchy based on dimension that you will select to construct your customer hierarchies. For example assume if the fact table looks like this.

As of date

Month

Quarter

Year

Branch

Area

Region

No of Cust

Total Sales

         


 

This table is no more fact table and we call it a detail table.


 

Scope of analysis:

Now after we finish bulding our univers let go and build our report. Assume that we just need to build a very simple report like this

Year

2008

2009

Region

No of Cust

Total Sales

No of Cust

Total Sales

center

342

23432

234

23432

west

234

23424

424

324


 

Create a new report based on our universe that we build earlier. Select region, year, number of customer and total sales then run the report. After that click on the drill icon (Analysis --> Drill) and then right click on any of the selected dimension (region, year) you will find that there is no option to drill by.

Now edit data provider (Data --> Edit data provider) and click view SQL button for the tool bar. You will find that the data selected are just the four columns that we have selected. Now to find what Scope of Analysis do click on the Scope of Analysis button and select both time and branch hierarchies. Please note that you can expand the hierarchies build before and select a specific dimensions. Let's assume that you want to analysis your figures by quarter, and year only and you are not interested in monthly figures or daily figures. Then all what you need is to put a check mark in the check box beside year and quarter in the time hierarchy tree when you open the scope of analysis window.

Now click again on view SQL button and note that all dimension with a check make beside in scope of analysis added to your SQL statement even if you didn't select them in the query builder window (remember that we have selected 2 dimensions [region and year])

Run the report after that.


 

Drill:

After running the report click on drill icon (analysis à drill) and then right click on region you will find that you can do the following:

  • Drill down: will go to the second level of the hierarchy. In our example it will display the area information and note that there is a drill down filter (combo box) displayed in the drill down filter tool bar. If you right click again on are and select drill down you will go to the second level (branch) and so on. If you filter on a specific region from the drill down menu it will just display the selected region date.
  • Drill up: will go a level up. If you were in area and click on drill up it will return back to region.
  • Drill by: a menu will displayed contains all hierarchy that you have selected in scope of analysis. The menu should be like this if you right click on region dimension
  • Drill by:
    Area
    Branch
    ----------
    Quarter


 

  • Drill through: to select another dimension that you didn't select in scope of analysis. In our example it will be month because we select year and quarter only from the time hierarchy tree.


 

  • Scope of analysis: if you want to update hierarchies that you want to analysis your data according to.
  • Set as drill filter: make this column a drill filter that you can filter your report data from it.


 

1 comment:

  1. This comment has been removed by a blog administrator.

    ReplyDelete