Study on the possibility of automatic conversion between BusinessObjects Universes and Oracle Business Intelligence Repositories Olti QIRICI Department of Informatics Faculty of Natural Sciences, University of Tirana Tirana, Albania olti.qirici@fshn.edu.al Abstract IV. User Interface Vast majority of business BI solution today has all The decision on which Business Intelligence of them implemented. At least the products which we solution should be selected is very difficult. are discussing here, have them all implemented. Sometimes it is required to switch between So which might probably be the most important these solutions. Here we will try to check reason for choosing a specific product could be the the features that some commercial products price of a specific product or how strategically the provide us, that help automize the process of choice of a given product might be combined with models creation. We will check for possible specific discounts on sales prices and favor a choice mappings between these solutions toward a over another. Generally BI products are quite final automized conversion process which expensive which might make even small discounts would simplify the process of products considerable. selection without enhanced efforts on Sometimes, enterprises prefer to choose one models conversion. Both ways of solution over the other because during the process of conversion have been investigated and are infrastructure transformation, it is required for the feasible but currently it seams that one of sake of uniformity to switch between different the automation ways is possible with least platforms. If a company or state enterprise decides to effort. change the technological providers, business intelligence tools, which represent these Keywords — BI, BO, BusinessObjects, OBIEE, transformation from the end user prespective on universe, repository, automatic reporting and analysis, would not escape these major changes, even for the reason of uniformity. 1 Introduction But BI models require a specific amount of time to be developed. As each information technology The process of selecting which Business project, this time is divided in requirement Intelligence tool will be used is not just a matter of specification gathering, design of the solution, which technical solutions fits most to a current development, testing and maintenance. When the situation. It is true mainly because all the possible BI management changes strategy and product, of course solutions, try to provide all those tools and a migration of the BI model is needed. But not all functionalities which are necessary for these systems. these steps have to be performed as a whole, while the Sometimes these tools might provide some extra longest step during the migration might be the features which make it simple to perform some development phase. In order to shorten this phase, it is operation. According to Turban et al [Tur11] there are required to produce an automation tool which would four main features which are prerequisites for a perform this transfer with little supervision required. system to be considered as a BI solution which are: This automation could be both ways or single way I. Data Warehouse extracting information from a product and II. Business Analytics recomposing it through feeding the other product. By a. Reports and queries so doing, it would be simpler to facilitate the decision b. Data, Text and Web mining and other on business intelligence solution migration without sophisticated mathematical and statistical tools major changes. III. Business Performance Management Even partial migration would be welcomed since sources, how these sources are interlinked and how the manual recreation of such solutions is generally a this information is mapped to business rules. long task with heavy handworks. Saving of time to The BO Universes and the OBIEE Repositories are the IT department would enhance their acceptability designed through some specific tools, part of the of the change and would replace the efforts on the product which follow up the design process through other phases of the migration project. these layers. These tools respectively are called as First lets have a look on each of the products we Universe Designer and Administrator Tool. Both are referring to. these tools are desktop applications which are used generally by a single user, even though there exists 2 BusinessObjects compared to Oracle collaboration techniques and configurations which might be used in the cases of distributed development Business Intelligence environment. But this collaboration is not very easy Business Objects is the major product developed and sometimes it requires manual merging of the by SAP which provides it’s users with the most changes done by concurrent developers. advanced Business Intelligence features. It fulfills Hereafter we will specify the way these with specific tools all the requirements for developing applications organize the information in multiple the business intelligence basics. It is possible to build layers and how all this information can be developed queries (free hand SQL queries or through the in these tools from the data sources to the presentation metadata creation), this can be fed through a data manifestation of these data. warehouse, additional data, text and web mining These tools have been used for long times now and techniques can be implemented for a more accurate no information is given by the companies which analysis of an unstructured information and it has a might make developers, integrators and researchers SAP BPM tool. Of course it is enriched with a think that these tools will be switched by other tools graphical user interface which is depending on the (possibly web applications) or these tools will become accessibility needed for each of the tools. It has deprecated in the near future. desktop developed tools, basically tools required to Let us analyze this multilayer designed perform heavy, not-collaborative tasks which require environment. a single user to utilize it at a time. It also provides web components or tools for the tasks which are used 3 Layers of the metadata design massively by the users and which are basically used for analysis and report generation. Currently, this SAP Both the Administration Tool for OBIEE and the product has reached the version of BO 4.2. Universes Designer used by BusinessObjects, are Oracle Business Intelligence from the other side is separate tools used to design the metadata repository the major Business Intelligence tool developed by and are not part of the process of generating the Oracle. The current version is 11g. reports and analysis. Actually they produce the core “Oracle Business Intelligence Enterprise Edition feeder for the intermediate layer of a OBIEE system, 11g (OBIEE) is an unmatched and comprehensive which is the information holder on the data business intelligence and analytics platform that organization and relationship. delivers a full range of capabilities including Following is an image showing the Administration interactive dashboards, ad hoc queries, mobile Tool which as can be viewed as a tool peripheral to analytics, notifications and alerts, enterprise and the system but as producer of the Universe, creates financial reporting, scorecard and strategy one of the main elements used by Oracle BI Server, management, business process invocation, the Server which extracts from the Data Sources, the unstructured search and collaboration, integrated requests from the clients. systems management and more.” [Ora13] This shows basically main features offered by OBIEE which are comparable to those offered by BO. What we have to be focused here it is the metadata which are used by these products in order to feed the tools when data are not directly extracted through free hand SQL. These metadata organizations are called Universes and Repositories respectively for the BO and the OBIEE. This intermediate layer between the different data sources and the processed information for an organization is a specific complex multitier metadata which stores information on these data Hereafter we will be referring to the physical layer as the database layer since for this conference I have been focused mainly at databases as a source. Both systems design their solution based on a group of elements which populate the various layers of their interface. There is a nearly one to one conversion of elements between OBIEE and BO. These elements will be matched hereafter following their specific layer belonging. So from the very basic (physical layer) to the presentation layer the most important elements might be (at least for a not so complex environment – since for complex tasks additional efforts might be executed manually on the environment to decrease the criticality of executing Figure 1. OBIEE architecture [Ger16] such transformations): In the following image it is shown that universes OBIEE element BO element created from the Universes Designer are central also BusinessModel Classes to the process of serving the client when referring to ConnectionPool Connection the BusinessObjects architecture. Database Database Dimension Dimension Measures Measures Details N/A LogicalComplexJoin Joins LogicalTable Classes LogicalTableSource N/A PhysicalDisplayFolder Folder PhysicalTable Tables, Views, Derived Tables, Synonyms PresentationCatalog N/A PresentationHierarchy Hierarchy PresentationTable Classes Figure 2. SAP Business Object Designer Schema Schema Architecture Diagram [Com] Table 1. Comparison between some of OBIEE and But not just the Business Intelligence system is a BO elements multi-layer system itself. Also the tools provide the model through a three major layers. As it can be seen by the above table, the number of  Physical Layer elements specified by OBIEE is quite major from  Business Model those of BO. But this doesn’t make BO less suitable  Presentation Layer as a semantic layer interconnecting the Data Source In order to have a working solution, the with the user. For some of the elements there is seen information should be passed through all these layers. no distinction between the presentation layer and the All the three layers should be populated with the logical layer in BusinessObjects. In the literature, accurate information in order to provide a correct BusinessObjects is referred as “the powerful semantic migrated metadata model between two different BI layer which lets you create complex SQL statements, models. without you ever needing to know or write SQL” These layers describe specifically the physical [How12]. Anyway from this standpoint, it can clearly model, the business model and the mapping of be seen that even BO transfers the information information technical-wise and functional-wise, and through structures from the physical layer to the the presentation layer which describes the model as it presentation one, since it creates independent is viewed by the end user which is not required to information gathering from the data source know the structure of the sources and their information. dependences. Following this logic, some information on different layers, clearly defined in OBIEE through different naming convention, in BO may be found under the same element. As was illustrated in the previous table, information on Classes (and sub-Classes unmentioned above) may be found in OBIEE as referring to logical and physical tables, as also can be linked to the Business Model itself. Furthermore, OBIEE allows a wider range of organizing the information in subject areas, folders and subfolders for each of the layers, while BusinessObjects provides a more simplistic approach. As it can be seen in the above table, some of the terms we specified for OBIEE does not have any equivalent in BO. Let us follow now with the analysis of the models for each of these systems. 4 Oracle Business Intelligence Model Figure 4. Conceptual relationship at the business end Following the previous discussion, here is shown a sample of built repository from a subset of tables It is necessary to develop in the Administration regarding the PC Module from Oracle FCUBS 12.2. Tool a three layered structure which covers the The schema regarding the relationship between these information flow designing which will map the tables has been illustrated in this image. physical stored data to presented information, easily accessible by the user, straight through a business model which interlinks both ends. In the following image we can see the result on such application for the above mentioned data model. This design will be used to explore the XML structure produced by the Oracle Administration Tool which describes this repository. Figure 3. Relationship between tables in a subset of the PC Module According to the business rules predefined by the need on querying information out of this partial database, a conceptual schema has to be designed on the result at the business layer. Figure 5. Three tier layer for the PC module in the Administration Tool In the above image can be seen the three layers (from right to left: i) Physical Layer, ii) Business Model and Mapping and iii) Presentation Layer). This solution was developed following the sample from the Oracle manual [Ora12] suggests. In order to produce an extract, which might be used as a universally integrated structure, without extracting the information from the repository compiled file of Oracle, the only solution would be to Tool itself. At least this solution might be convenient export this compiled file to a readable format which if the conversion from BO universe to OBIEE might be created and exported, every time such task repository is done once and the maintenance of the would be required. This option is a project of XML repository is done through the Administration Tool files, commonly known by Oracle as MDS XML always utilizing the compiled repository (.rpt file). Documents. These files are simple XML files, organized in folders which specify the different 5 BusinessObjects Model elements which should be defined to build a Business Object also follows a very wide variety of repository. These folders, which are then interlinked integration and development kits in order to allow by IDs, uniquely identifying these elements and also users an automated access. As per SAP which identify specific elements inside the XML files documentation site there are provided “developer are alphabetically ordered as: BusinessModel, guides, API reference material, sample code, and ConnectionPool, Database, Dimension, Group, object model diagrams for Java, .NET, COM, and InitBlock, LogicalComplexJoin, LogicalTable, Flex APIs available in SAP BusinessObjects BI LogicalTableSource, PhysicalDisplayFolder, 4.x”[Leo16]. PhysicalTable, PresentationCatalog, The simplest way which might be used to PresentationHierarchy, PresentationTable, Schema document and develop, without using the Business and Variable. Object Universe Designer would be to take in consideration the Excel VBA library provided for BusinessObjects. T This library is called BusinessObject Designer 12.0 Object Library and can be download in the internet but which is also installed during the Designer installation on a Windows OS running machine. The usage of this tool is based on two major objects called Designer.Application and Designer.Universe which execute methods in order to get and set specific elements for the Designer. This tool requires authentication to BusinessObjects in order to proceed with any action, making the access really secure. Figure 6. Structure of folders for a MDS XML For sampling on using these SDKs for Business Documents project Objects, several online sites can be consulted. [Hil10] What these folders specify can be viewed basically 6 Migration of the universes to by reflecting on the name. Files inside these folders repositories and of the repositories to are named by an element id having as naming universes convention element_id.xml. Inside these XML folders, are specified in XML tags (unfortunately BusinessObjects allow the users to access and undocumented or unshared documentation by Oracle) modify information on a Universe, so it is possible to the specific information required to fully define each define a new BO Universe starting from a pre- element. extracted OBIEE repository, which should be simple What is also unfortunate and which seam such a to read, as suggested by me in the previous chapter a strange behaviour, is that the MDS XML documents MDS XML documents structure, which might be read created in such way from the Administration Tool from any of the third generation languages of which cannot be imported back in the Administration Tool SAP provides the SDK, and then the data fetched itself (at least for the 11.1.1.9.0 which is the last tool might be used to create the required repository. Oracle has shared till the moment this paper was From the other side on, these tools (libraries) written). Following several attempts, I concluded that provided for BusinessObjects, allow us to document a for some of the files, Oracle does not produce a whole universe, in such a detailed way that the unique ID. This duplication of IDs fails to open the information might be used to recreate the same MDS XML files and the opening of the whole project. metadata structure which was previously stored inside Anyway, this problem might be bypassed in case the Universe itself. these files are created through some other script, and A third generation development language might be are not directly created through the Administration used, I propose a language with a strong XML support, to take this information which might have through their IDs sometimes giving name to elements been stored in files, or even directly through the SDK, inside the documents hierarchy folders. and after compiling a MDS XML Documents project, These automation tools would allow the users to to upload this one for creating a compiled repository switch between these Business Intelligence solutions, which might be used for further modifications from taking in consideration other aspects for the choice OBIEE Administration Tool (since the export of the than the migration of metadata between the two Administration Tool in MDS XML does not allow the solutions. re-uploading of this document, without recreation and So, little to no efforts it would be needed in a one replacement of the IDs as described in a previous to one migration, in case when the migration itself paragraph). wouldn’t require the redesigning or the fine-tuning of the solution. 7 Conclusion It is possible to build a tool which would facilitate References the migration between the two Business Intelligence solutions, BusinessObjects and Oracle BI. One side, the BusinessObjects side is fully automated and [Tur11] Turban E., Sharda R., Delen D., King D. functional, while the Oracle BI part still has some Business Intelligence – A managerial approach, small problems while producing the MDS XML Second Edition, 2011, pp. 327. Documents project directly from the Administration [Ora13] Oracle Business Intelligence Suite Tool. This problem makes it simpler for users to Enterprise Edition 11g - Data Sheet. (2013). switch from BusinessObjects to Oracle BI, while for Retrieved from http://www.oracle.com/us/bi- the other way, IDs of XML elements and folders have enterprise-edition-plus-ds-078848.pdf to be recreated before usage. The problem with exporting the MDS XML [Wha09] What is a BusinessObjects Universe. (2009, documents, problem with the ID uniqueness which is March). Retrieved from very important in XML elements identification, also http://alteksolutions.com/wp/index.php/2009/03/ makes it difficult for OBIEE repository creators to what-is-a-business-objects-universe/ export and re-import the documents themselves. This [Com] Compass Information Science. means that when producing a repository from a http://www.wcompass.com/ SupplyProduct.aspx universe, all migrations should complete in one hand, without making further transformations on the [Ger16] Gerardnico. (2016, June 15). Retrieved from repository itself,since this change repository could not http://gerardnico.com/ wiki/dat/obiee/bi_server be exported further more, making all remaining [Ora12] Oracle Financial Services Software Limited, changes subject to manual transformations. Oracle FLEXCUBE Universal Banking® 12.0 Even though we mentioned it that one of the ways OBIEE Repository Development Guide, Release is simpler, the other way is not impossible. Just the 1.0, May 2012 creation of the universe from the repository should ignore the existence of the IDs created by OBIEE and [Leo16] Leong, K., Moitel F. (2016, January 6). SAP regenerate IDs in order to determine elements. IDs BusinessObjects BI 4.x - Developer SDK would not be a major problem since the identification Library. Retrieved from of the different elements can be done also by other http://scn.sap.com/docs/DOC-27465 attributes (ex. the name of the element). [Hil10] Hilton, D., Stone, A. (2010, May 10). NET The automation tools have to be developed in RAS SDK Samples. Retrieved from technologies which should from one side interact with https://wiki.scn.sap.com/wiki/display/BOBJ/ the SDKs BusinessObjects provide (for some of the most popular third generation languages as Java, .Net [How12] Howson C., Newbould E. SAP etc.) and from the other side should have capabilities BusinessObjects BI 4.0 The Complete Reference, of simply validating and interpreting XML documents Third Edition, McGraw Hill Professional, 2012, and writing XML documents through specialized pp. 551. functions. These XML elements should be linked