Technical Methods to Ease Migration from Hyperion Interactive

TECHNICAL METHODS TO EASE MIGRATION FROM HYPERION INTERACTIVE REPORTS - DECEMBER 11, 2018 1

Technical Methods to

Ease Migration from

Hyperion Interactive

Reports

December 11, 2018, ADAL Sprint 187

Office of Systems

Advanced Data Analytics Lab

Patricia Stanton

TECHNICAL METHODS TO EASE MIGRATION FROM HYPERION INTERACTIVE REPORTS - DECEMBER 11, 2018  2 
Table of Contents 
I.  Overview ................................................................ 3 
Background ........................................................................................................................................................................................... 3 
Easing the Migration ........................................................................................................................................................................... 3 
II.  Analysis – Creating the Data ................................. 4 
Components of an Enterprise Report Solution ............................................................................................................................. 4 
The EPM Database ............................................................................................................................................................................. 5 
How many documents do you have? ................................................................................................................................... 5 
Section information for each document .............................................................................................................................. 7 
Section Dependency................................................................................................................................................................ 8 
Data Model Section Type ....................................................................................................................................................... 9 
Data Model Section Types with Meta Topic Tables ....................................................................................................... 16 
Query Section Type ............................................................................................................................................................... 17 
Results Section Type ............................................................................................................................................................. 20 
Import Section Type ............................................................................................................................................................. 22 
Table Section Type ................................................................................................................................................................ 23 
Identifying DataSources ................................................................................................................................................................... 23 
Restful Web Services ......................................................................................................................................................................... 24 
Converting Query Section Type to WebFocus ................................................................................................................ 24 
III.  SMILE - Visualizing the migration metadata .... 28 
IV.  Resource Savings .................................................. 31 
V.  Conclusion ............................................................ 31 
VI.  Appendix ............................................................... 31 
Appendix A – EPM Tables .............................................................................................................................................................. 31 
Appendix B – Code to Create DocumentContent and populate with section data .............................................................. 33 
Appendix C – Python Script to upload file to WebFocus using Restful Web API ............................................................... 34 
 
 
 
 

TECHNICAL METHODS TO EASE MIGRATION FROM HYPERION INTERACTIVE REPORTS - DECEMBER 11, 2018 3

Federal agencies can make use of relational database technology, scripting languages such as Python, and restful web

services to ease the burden of report migration from one enterprise solution to another.

Overview

Background

Besides the programmatic applications that power the core mission, enterprise-reporting applications are important to

provide management information that federal agencies need to

make informed decisions when planning goals, workloads, and

resources. When one thinks of enterprise report offerings, the

vendors that most commonly come to mind are Crystal Reports,

Tableau, WebFocus, and Hyperion. The enterprise report

landscape has changed key players and solutions quite a

bit. Agencies face challenges with migrating from one enterprise

report solution to another. There are different reasons an agency

must migrate to different solution and it can be because a vendor has decided to discontinue support or the agency has

decided that a different product is more in line with its needs.

The Social Security Administration (SSA), over the past decade used Hyperion’s Enterprise Performance Management

(EPM) System for some its Management Information and Reporting needs. The EPM product had an interesting

lifecycle over the last twenty years. It originated from a company called Brio Software who called the product Brioquery.

Hyperion purchased Brioquery and rebranded the product as such. Oracle then purchased Hyperion in 2007 and

supported the product for over a decade. Oracle has now decided to sunset the product to focus on its other business

intelligence solutions.

SSA has over fifty thousand reports housed in EPM. Employees have labeled over three thousand reports as critical

reports that must be migrated. With the evolving technical landscape concerning big data and cloud computing, SSA is

moving to a mixture of products to support its management information needs.

Easing the Migration

The primary goal is to sunset the EPM solution. EPM customers that

have any report that they need to continue to use must be migrated to

another solution. When one is considering that thousands of reports

are involved, this is a considerable workload to recreate each of these

reports manually.

The Advanced Data Analytics Lab (ADAL) is a small team of Data

Scientists, Developers, and Analysts that provide a variety of analytic

and ‘technology proof of concept’ support within SSA. Outlined in

this paper are technical methods as recommended by ADAL that SSA

and federal agencies can use to leverage scripts and restful web

services to help track and ease the administrative and development overhead of the migration process.

TECHNICAL METHODS TO EASE MIGRATION FROM HYPERION INTERACTIVE REPORTS - DECEMBER 11, 2018 4

Analysis – Creating the Data

Components of an Enterprise Report Solution

Enterprise Report Solutions provide a framework for customers that typically consist of the following features, which

enable one to:

 Create data connections to a variety of data sources, which can include databases, spreadsheets, or text files.

 View, retrieve, manipulate, filter, and sort the data.

 Display the data in a variety of ways in reports, tables, pivots, dashboards, or graphical views.

 Share reports and content with other users.

 Export the data to variety of formats including pdf or spreadsheets.

 Control who has access to the each of the features, reports, and content.

In order to provide this framework, most Enterprise Report Solution have the following components:

 Database to track details about the reports, data content, users, and user privileges.

 Web front end or desktop client application for users to access, create, edit and share reports.

 Web or other front end for administrators to maintain solution and grant user privileges.

 Restful web services or application programming interfaces (APIs) to enable administrators to perform

maintenance activities on bulk set of objects such as moving reports from one virtual folder to another,

uploading new content, or archiving outdated content.

EPM consists of a database, web front end, web administration tools and restful web services to provide the enterprise

report features and this is true of other well-known enterprise report solutions such as WebFocus, Tableau, and SQL

Server Reporting Services (SSRS). If you have access to the database, you can theoretically reconstruct much of the

folder information, list of reports, and some of the content. Afterwards, you can pair this process with the restful web

service APIs of the target solution to migrate the content.

When exploring and leveraging the solution database, it is best to work with a copy of the database and not the live one

as you could unintentionally break the application if you change any data. Vendors usually prefer that customers leverage

their technical staff for database related work through consulting or support hours. I worked with a snapshot of the

database and not the live production database for this effort.

You can download a document on the database model from Oracle’s Support Site at

https://docs.oracle.com/cd/E40248 01/epm.1112/epm data models.zip. This link downloads a zipped file that

contains several documents on the suite of Hyperion Products. The file pertinent to this analysis is the

ReportingandAnalysis.pdf. The document provides a list of all the tables, columns, primary keys, foreign keys and what

those keys link to which is helpful. It does not contain descriptions of the tables or columns so we really had to put on

our investigative hats on and do quite a bit of data exploration to make sense of the database structure.

TECHNICAL METHODS TO EASE MIGRATION FROM HYPERION INTERACTIVE REPORTS - DECEMBER 11, 2018 5

Fortunately, there is comprehensive documentation for the Restful Web Services Application programming interface

APIs. Vendors provide these services to support administrative and developer needs and encourage their use. They also

provide documentation, code samples, and training to customers.

The EPM Database

I have already cautioned you about using care when working with the database so be sure to work with a copy and not

the live database. Office of Systems houses the production database on an Oracle server. I ported much of the data to a

SQL Server database. One could use another Oracle Database, a MySQL database or even a NoSQL database if

preferred. I picked SQL Server 2017 because of the string functions that made it much easier for to reconstruct the

report content and reduced the amount of code I had to write. I will point out the portions of code that leverage those

new functions where applicable.

The EPM database has hundreds of tables. I extensively reviewed the tables and narrowed down the list of the most

critical tables to this effort to fifty-five tables as shown in Appendix A. In EPM, you refer to all reports as documents.

Each document contains one or more sections. The sections can be data models, queries, results, reports, pivots, charts,

or dashboards. Sections can also be dependent on each other. For instance, one query may derive from another query,

which may derive from a data model.

How many documents do you have?

The V8_CONTAINER table appears to be a master table within the database for all objects including but not limited

to folders, documents, and open catalog extension (OCEs). OCEs are the objects that define the data source and

connection information. Other enterprise report solutions use the term data source, data adaptor, or database

connection instead of OCE. The CONTAINER_UUID is the primary key for this table and contains the unique

identifier for each object. The CONTAINER_UUID column connects to the foreign key UUID column in V8_OCE

and V8_CONT_VERSION tables.

The V8_CONT_VERSION table uses both the CONTAINER_UUID and VERSION_NUMBER columns as a

primary key and these columns are used together to connect to the matching foreign key columns in the

V8_H_DOCUMENT, V8_QRY_DB_CONN, V8_BQ_SECTION, and V8_FOLDER tables. These tables are

important tables that contain information about objects, data connections and virtual folders.

Let us take a deeper look at how document information is stored and how we can identify the document sections and

content. The table V8_H_DOCUMENT contains a row for every document as shown in the image below. The

V8_CONTAINER table will also have a row for every document but the V8_CONTAINER table contains unique rows

for other objects such as folders and OCE rows in addition to documents. The V8_H_DOCUMENT only contains

rows for all documents, not any other objects.

The DOCUMENT_ID column is the primary key column and uniquely identifies each document in the

V8_H_DOCUMENT table. To get a count of how many documents we have I can run the query below which returns

in this case, 57,665 rows.

TECHNICAL METHODS TO EASE MIGRATION FROM HYPERION INTERACTIVE REPORTS - DECEMBER 11, 2018 29

at http://<smile_url>/Documents.aspx . Below is a screen shot:

also provided a way to drill down into each section and then view the recreated content:

This application is a C# MVC (model-view-controller) application written in Asp.Net and hosted on a windows web

server within the Office of Systems in the SHE environment. Its data is stored on a SQL Server data hosted on a SQL

Server 2017 server within the Office of Budget, Finance and Management.

This application implements the DevExpress grid and charts that consist of bar, pie, and area charts. There is also a

dashboard that shows a summary of migrated reports.

(b) (6)

TECHNICAL METHODS TO EASE MIGRATION FROM HYPERION INTERACTIVE REPORTS - DECEMBER 11, 2018 30

TECHNICAL METHODS TO EASE MIGRATION FROM HYPERION INTERACTIVE REPORTS - DECEMBER 11, 2018 35

##define the api endpoint and user credentials

devuser = 'sampleuser'

devpw = 'samplepassword'

strbaseurl = 'http://<webserver name>/ibi_apps/rs/ibfs' #change to actual webfocus URL

wfDevURL = strbaseurl + '?IBIRS_action=signOn&IBIRS_userName=' + devuser + "&IBIRS_password=" + devpw

#define folder to be migrated

mgfolder = '/BI Central Office Folders/DCBFM/DCBFM FPA/Development/migrationScript_test'

getdocsSQL = "select document_id from [V8_H_DOCUMENT] where path = '" + mgfolder + "'"

#define function that when called, does all the work needed

def signonWF():

response = requests.post(wfDevURL)

status = response.status_code

if status == 200:

print('successfully connected ', response.status_code)

r = response.text

#r.write('output.xml')

cookie = response.cookies

#print(cookie)

root = ET.fromstring(r)

tree = ET.ElementTree(root)

#write to xml file so that minidom can parse it

tree.write("output.xml")

dom = minidom.parse("output.xml")

xmltags = dom.getElementsByTagName('entry')

stoken = [items.attributes['value'].value for items in xmltags if items.attributes['key'].value ==

"IBI_CSRF_Token_Value"]

print('security token is ', stoken)

mdocs = pd.read_sql(getdocsSQL,con=conn)

d = 0

for row in mdocs.values:

docid = row[0]

#identify all query sections that have webfocus content for each document

secsql = "SELECT concat(replace(d.DOCUMENT,' ','_'), '_sec_', replace(s.SECTION_NAME,' ','_')) as docTitle, \

[WebFocusText] FROM [dbo].[DocumentContent] dc inner join [V8_H_DOCUMENT] d on dc.DocumentID =

d.DOCUMENT_ID \

inner join [V8_H_SECTION] s on dc.SectionID = s.SECTION_ID where not [WebFocusText] is null and not

WebFocusText = 'NO DATA' \

and s.SECTION_TYPE_ID = 8 and dc.documentid = " + str(docid)

wfData = pd.read_sql(secsql,con=conn)

i = 0

for row in wfData.values:

#get report title

description = row[0]

filecontent = row[1]

byteobj = base64.b64encode(filecontent.encode())

fileobj = byteobj.decode("utf-8")

#print(fileobj)

TECHNICAL METHODS TO EASE MIGRATION FROM HYPERION INTERACTIVE REPORTS - DECEMBER 11, 2018 36

reportpath = '/WFC/Repository/BIMigration/uploadtest/' + description + '.fex'

strtoken = stoken[0]

secToken = '&IBIWF_SES_AUTH_TOKEN=' + strtoken

putObj = '<rootObject _jt="IBFSMRObject" description="' + description + '" type="FexFile"> <content

_jt="IBFSByteContent" char_set="Cp1252">' + fileobj + '</content> </rootObject>'

upURL = strbaseurl + reportpath + '?IBIRS_action=put&IBIRS_object=' + putObj + secToken

#print(upURL)

uploadresponse = requests.post(upURL, cookies=cookie)

#file.close

#print(uploadresponse.text)

#increment for next section

i+=1

#increment for next document

d+=1

else:

print('Not able to connect to webfocus, the status code is ' + status)

##connection string to migration database

conn = pypyodbc.connect("DRIVER={SQL Server};"

"SERVER=<database server>;"

"DATABASE=<database name>;"

"Trusted_Connection=yes;")

cursor = conn.cursor()

#call function to get documents and perform migration

signonWF()

#cleanup database connection

conn.commit()

cursor.close()

conn.close()

print("all done migrating for this folder, please check the webfocus server")