Learn Hyperion/OBIEE by AMIT SHARMA


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

Advertisements
This entry was posted in Hyperion Brio and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s