Archive for the ‘Hyperion Brio’ Category

Building Sales Analysis Dashboard using Hyperion Interactive Reports

May 21, 2010

Building Sales Analysis Dashboard using Hyperion Interactive Reports


We’ll see today how to build Sales Analysis Dashboard using Hyperion Interactive reports. During the session I’ll be touching various components of Hyperion interactive reports and how it can be used to build rich formatted reports. We’ll see the step by step approach to create.

1)       Understanding the business requirements.

2)       Design the structure of the requirements

3)       Building the database connection.

4)       Designing the Data Model [Metadata Modeling]

5)       Creating queries and tabular reports

6)       Defining the custom column(s)

7)       Designing and formatting the Dashboard

8)       Incorporating the custom control.

9)        Adding scripting to the Dashboard

10)   Publishing reporting in the Dashboard.


Business Requirements:

As a business I was the excel sheet and asked to build the rich web based reports. The excel sheet business users were using for many year and reluctant to move to a new environment despite the current system was full of flaws and high prone to error. There were so structured approached to store and manage the data, people used to pull data from custom based .Net application and do local desktop analysis. The method was so simple just pull data from custom Sales Order Process system and export data into excel for further analysis.

As exiting reports are best means to start up requirement analysis. These reports gives you clear picture about what people like to see. However, when working with such reports you need to pay significant attention to the flaws associated with the current system.

Design the structure of the requirements:

It took couple of days to me to pull the relevant table/data structure. The customized application had close to 120 tables which spans to customer, product, region, transaction, order, shipping information. In order to serve the current request I needed to do dimensional modeling and bring dimension and fact concepts from transaction based current system.

Finally I designed the model from transaction system to OLAP model consists of Dimension and facts. The proposed model is as given below.


While doing physical modeling we identified the following dimensions and facts.

1)       Customer

2)       Products

3)       Orders

4)       Sales Person

5)       Time Day

6)       Market

And the fact table is factsRev (Fact Revenue). The above model is snowflake schema. Once we designed the model and ensure we have all to go ahead and build our reports.

Building the database connection :

Create a new database connection File (OCE) open database connectivity

The below type of driver Hyperion IR supports

Option Description
What connection software do you want to use? Select the connection software to use when connecting to the database.Depending on the connection software, additional fields may be displayed. These fields enable you to customize the Interactive Reporting database connection file; show metadata settings; and select ODBC logon dialogs.
What type of database do you want to connect to? Select the type of database to which to connect from the pull-down list.

To bring the data from the data source to the report the first step is always to connect to the data source. IN Hyperion IR we create OCE file which is connection file. The OCE file store the connection related all the information. However, the OCE does not store the password.

The next step is to create a data model same as it is provided to you by the data modeler. Sometimes we need to make some changes into the logical modeling. For example in the given model we may need to create multiple instance of Customer Dimension to present. It is always a good practice to create a master data model above this logical model. This reduces the complexities and gives simple view to the developer.

Preferably one should create a logical view upon this model.

So my next step is to create a Master Data Model and hide the complexity and provide business view to the model.

The benefit of converting the current model to Master Data Model is that any changes to the master data model get propagated to all dependent queries that are based on the master data model. Each time a new query is inserted into a Interactive Reporting document file that contains a master data model, you are prompted to link the new query to the master data model. When a query is promoted to a master data model, it is added to the Section pane as a new section. Once you promote a query to a master data model, you cannot undo it.

Now your query model got frozen and it follows all the changes you do in Master Data Model.



Now let’s create a simple view over this complex model. I’m going to add new view [Add Meta Topic]

DataModel → Add Meta Topic


“Revenue By Sales Person” is a user define View in IR. The whole idea is to simplify the way model looks to the business. Now all relevant columns exist in the model. The report designer has just to see this model to design the report, rather than fining the column from the complete column.

Similarly “Revenue By Region” “Revenue by Product” and “Revenue by Customer”.

Once we designed the Meta Topic model, its time to design the actual query for four different kind of analysis we need. Finally we’ll place all the query result in Dashboard and will give integrated look to all the reports.

Once the query is designed, the next step is to process the query and capture. The output of the query is given below.

Similarly we need to create query for other 3 subject areas.

1)     Revenue by Customer.

2)     Revenue by Product.

3)     Revenue by Region.

However the users want output in chart format so we need to create one chart for all the reports and place them all into Dashboard.

Use option Insert→Chart

A Hyperion interactive report support rich formatting in chart and has many kinds of chart to suite your business requirements. One can change the properties based on business needs. In the given business requirement business wants to see the horizontal bar chart.


Make sure you select the fact in fact layout and dimension(s) in X or Y Axis. Incorrect selection may leads to hang your system, depends upon the size of fact data. Similarly create chart for all the other 3 reports.

It’s time to create a dashboard and incorporate all the Charts into a single dashboard and give users a integrated view to the data.

Insert Dashboard.

Insert→New Dashboard


After inserting chart, you can place the chart into dashboard simply by selecting chart and dragging and dropping into dashboard. The way the charts are looking is not good, you suppose to format it and place appropriate heading.

So finally your IR Dashboard look like this way.

In the second of the document we’ll talk about incorporating Dashboard Java Script.

For training and support on Essbase , Planning, Financial Reporting, Web Analysis Reporting and OBIEE reports pl mail me on aloo_a2@yahoo.com.


You can also download the video tutorial from my other blogs

learnhyperion.wordpress.com

Learn Hyperion/OBIEE by AMIT SHARMA

May 20, 2010


Managing Metadata In Hyperion Interactive Report(Brio):-

A data model, a focused visual representation of the actual database tables. A Data Model displays database tables as topics in the Contents frame. Topics are visually joined together like database tables and contain related items used to build a query. If the same Data Model wants to use for future queries, you can promote it to a master data model and lock its basic property design. This feature enables you to generate future queries without having to recreate the Data Model. An Interactive Reporting document file can contain any number of master data models from which any numbers of queries can be generated.

The below example is created based on Sample Database available with IR.

To create a Data Model like given above, just select the table(s) and drag and drop on content pane. And join them based on the information availble in Data Model Documents in HLD.

Metatopics and metadata enable advanced users to mask the more confusing technical aspects of databases for non-technical users. While data models are already simplified views of the database, they sometimes still present a challenge to novice users. This is especially true when confusing database names and complicated strategies are visible in the data model.


Once the Data Model is ready to use and you want the same data model should be used for further queries so promote the data model to Master Data Model.

  • Metatopics Topics created from items in other topics. Metatopics are higher level topics, or “virtual topics” that simplify the data model structure and make joins transparent. A metatopic looks and behaves like any other topic and can accept modifications and metadata.
  • Metadata Data about data. Typically stored in database tables, and often associated with data warehousing, metadata describes the history, content, and function of database tables, columns, and joins in understandable business terms.

It’s become quite challenging to manage mapping between MetaTopic and actual table when we have large number of tables and the model become so complex. To eliminate the complexity and ease the work I have written a custom script, which will export all joining info and MetaTopic and actual Table mapping in excel sheet. Below the script given

Command Button for Generating the Metadata Mapping

Excel = new JOOLEObject(“Excel.Application”);

Excel.Visible = true;

Excel.Workbooks.Add;

Topic = ActiveDocument.Sections["MasterDataModel"].DataModel.Topics["Product Sales"]

var NumElements = Topic.TopicItems.Count

var k = 1

for (I = 1; I <= NumElements;I++)

{

a=Topic.TopicItems[I].DisplayName

b = Topic.TopicItems[I].PhysicalName

table = b.substring(0,b.indexOf(“.”))

column = b.substring(b.indexOf(“.”)+1)

Excel.Sheets.Item(1).Cells.Item(1).Item(k).Value = a;

//Excel.Sheets.Item(1).Cells.Item(2).Item(k).Value = table ;

//Excel.Sheets.Item(1).Cells.Item(3).Item(k).Value = column ;

Excel.Sheets.Item(1).Cells.Item(3).Item(k).Value = b ;

k = k+1

}


Command Button for Generating the Joining Info

Excel = new JOOLEObject(“Excel.Application”);

Excel.Visible = true;

Excel.Workbooks.Add;

var k =1

var operator_string = “”

join_count = ActiveDocument.Sections["MasterDataModel"].DataModel.Joins.Count

Console.Writeln(“Step 1″)

for(i = 1; i<= join_count; i++)

{

cnt=1;

Console.Writeln(“Step 2 “+i)

phy_left = ActiveDocument.Sections["MasterDataModel"].DataModel.Joins[i].Topic1Name

phy_left = ActiveDocument.Sections["MasterDataModel"].DataModel.Topics[phy_left ].PhysicalName.split(“.”);

db_left = phy_left[0];

table_left = phy_left[1];

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value = db_left

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value = table_left

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value = ActiveDocument.Sections["MasterDataModel"].DataModel.Joins[i].Topic1Name

Console.Writeln(“Step 3 “+i)

phy_right= ActiveDocument.Sections["MasterDataModel"].DataModel.Joins[i].Topic2Name

phy_right = ActiveDocument.Sections["MasterDataModel"].DataModel.Topics[phy_right ].PhysicalName.split(“.”);

db_right = phy_right[0];

table_right = phy_right[1];

Console.Writeln(“Step 4 “+i)

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value = db_right

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value = table_right

Console.Writeln(“Step 5.1 “+i)

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value = ActiveDocument.Sections["MasterDataModel"].DataModel.Joins[i].Topic2Name

//ActiveDocument.Sections["MasterDataModel"].DataModel.Joins[i].Topic2Name

Console.Writeln(“Step 5.2 “+i)

var oper

if(i!= 1)

{

if(operator_string == ActiveDocument.Sections["MasterDataModel"].DataModel.Joins[i].Topic1Name + ActiveDocument.Sections["MasterDataModel"].DataModel.Joins[i].Topic2Name)

oper = “AND”

else

oper = “ON”

}

else

oper = “ON” ;

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=oper

operator_string = ActiveDocument.Sections["MasterDataModel"].DataModel.Joins[i].Topic1Name + ActiveDocument.Sections["MasterDataModel"].DataModel.Joins[i].Topic2Name

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value = ActiveDocument.Sections["MasterDataModel"].DataModel.Joins[i].TopicItem1.DisplayName;

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value = ActiveDocument.Sections["MasterDataModel"].DataModel.Joins[i].TopicItem2.DisplayName;

var temp= ActiveDocument.Sections["MasterDataModel"].DataModel.Joins[i].Type ;

if(temp==7)

{

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”EQ”

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”LOJ”

}

else if(temp==8)

{

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”EQ”

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”ROJ”

}

else  if(temp==1)

{

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”EQ”

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”EQ”

}

else  if(temp==5)

{

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”GT”

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”EQ”

}

else  if(temp==6)

{

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”GE”

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”EQ”

}

else  if(temp==3)

{

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”LT”

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”EQ”

}

else  if(temp==4)

{

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”LE”

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”EQ”

}

else  if(temp==2)

{

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”NE”

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”EQ”

}

else

{

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”UNK”

Excel.Sheets.Item(1).Cells.Item(cnt++).Item(k).Value=”UNK”

}

k = k+1

}


And finally the Excel generated output is.

For training and support on Essbase , Planning, Financial Reporting, Web Analysis Reporting and OBIEE reports pl mail me on aloo_a2@yahoo.com.

You can also download the video tutorial from my other blogs

learnhyperion.wordpress.com

Learn Hyperion Interactive Reports

February 16, 2010
View this document on Scribd

Follow

Get every new post delivered to your Inbox.