One of the most important aspects of an OBIEE project is getting the design right. Of course you say, but how do I document it, and how do I update the documentation when the model is updated.
Many of you seasoned OBIEE developers will know about exporting the metadata from the RPD, from the database and from the Web catalog. This is fine, if all you want to document, is lists of objects.
The problem is, we cant give all our users a nice view of our data model in the RPD without giving them the Admin tool.
Until Now.
This post will demo how to show your data relationships and models using an Apex Application and a Neo4j Database. (we also use a javascript library called neovis.js)
Neo4j
We are all know about a relational database, or thought we did, but someone figured out that relationships can be more than one dimensional.
In a normal database a person object can relate to a car object. The person has a name and age and the car has a name and age.
But how do you show that a person owned a car for 10 years, and he loved the car? Here the relationship has attributes, the relationship is OWNED and has a property of 10 years and another property of LOVE.
So they invented NEO4j. A Graph database that store entities and their relationships. and both can have mulitple attributes. This type of database is perfect for us to display the relationships between tables in a database.
The other advantage of Graph databases is that they can be displayed graphically
Movie Database
The classic example that you get with a Neo4j install is the Movie database. I recommend that you have a play with it.
Anyway, in my systems I always load the metadata from the RPD and webcat into a database and use APEX to view or document it.
It was then a simple process of using the sysem to product data to load in Neo4j
The commands to create nodes and rlationships in neo4j are simple enough, here are some examples where I create a metadata model:
//DesignNodeType
CREATE (dntSemanticLayer:DesignNodeType {name:'SemanticLayer',description: 'Semantic Layer Objects. RPD File'})
CREATE (dntDatabase:DesignNodeType {name:'Database',description: 'Database Objects'})
CREATE (dntWebCatalog:DesignNodeType {name:'WebCatalog',description: 'Web catalog Objects'})
CREATE (dntBusiness:DesignNodeType {name:'Business',description: 'Business Names'})
CREATE (dntCLT:DesignNodeType {name:'CLT',description: 'Consume Load Transform Integration'})
CREATE (dntIntegration:DesignNodeType {name:'Integration',description: 'Integration Hub'})
CREATE (dntApplication:DesignNodeType {name:'Application',description: 'Applications'})
Create relationships
MERGE (dntSemanticLayer)-[:REPORTS_ON]->(dntDatabase) MERGE (dntWebCatalog)-[:USES]->(dntSemanticLayer) CREATE (dntDatabase)-[:POPULATED_BY]->(dntCLT) CREATE (dntIntegration)-[:PUBLISHES]->(dntDatabase) CREATE (dntApplication)-[:UPDATES]->(dntDatabase)
Examples of creating Metadata nodes
CREATE (dnSubjectArea:DesignNode {name:'SubjectArea',description: 'Subject Area',type:'Semantic Layer'})
CREATE (dnSubjectAreaTable:DesignNode {name:'SubjectAreaTable',description: 'Presentation Table',type:'Semantic Layer'})
CREATE (dnBusinessModel:DesignNode {name:'BusinessModel',description: 'Business Model',type:'Semantic Layer'})
CREATE (dnSubjectAreaColumn:DesignNode {name:'SubjectAreaColumn',description: 'Presentation Column',type:'Semantic Layer'})
CREATE (dnBIAnalysis:DesignNode {name:'BIAnalysis',description: 'Answers Analysis',type:'Web Catalog'})
CREATE (dnLogicalTable:DesignNode {name:'LogicalTable',description: 'Logical Table',type:'Semantic Layer'})
CREATE (dnLogicalColumn:DesignNode {name:'LogicalColumn',description: 'Logical Column',type:'Semantic Layer'})
CREATE (dnRPDPhysicalTable:DesignNode {name:'PhysicalTable',description: 'Physical Table',type:'Semantic Layer'})
CREATE (dnPhysicalColumn:DesignNode {name:'PhysicalColumn',description: 'Physical Column',type:'Semantic Layer'})
CREATE (dnBIDashboard:DesignNode {name:'BIDashboard',description: 'Dashboard',type:'Web Catalog'})
CREATE (dnBIAgent:DesignNode {name:'BIAgent',description: 'BI Agent',type:'Web Catalog'})
CREATE (dnDatabase:DesignNode {name:'Database',description: 'Database',type:'Database'})
CREATE (dnDatabaseTable:DesignNode {name:'DatabaseTable',description: 'Database Table',type:'Database'})
CREATE (dnDatabaseTableColumn:DesignNode {name:'DatabaseTableColumn',description: 'Database Column',type:'Database'})
CREATE (dnDatabaseView:DesignNode {name:'DatabaseView',description: 'Database View',type:'Database'})
CREATE (dnDatabaseSchema:DesignNode {name:'DatabaseSchema',description: 'Database Schema',type:'Database'})
CREATE (dnDatabaseMaterialisedView:DesignNode {name:'DatabaseMaterialisedView',description: 'Database Materialised View',type:'Database'})
Once we have the Neo4j data loaded, we can build an Apex app to view it graphically…
In the latest version of our Database Management System I have added a page that uses the neovis javascript lbrary to query the neo4j database. I made it simple to demo how it works, witha selector on the node labels.
First we creata a page with the selectior, a code block and a div, specifically named ‘viz’
The page header contains a refence to the neovis Javascript package that I loadded into the Static files area:
The code block does the connection and querying. Notice how I reference the page item P123_NODELABEL in the query
Now we add a dynamic action to the selctor that re-runs the vis code…
So here is the result, when I select the label DesignNodeType
You can alter the appearance of the nodes and the relationships..
Results can be limited or follow levels of relationship. You use CYPHER language to query your data.
The above is just the tip of the iceberg, and there are other javascript libraries that could do the job, my favorite is Graphlytics, but thats for another day!
Adrian
Note: To be fair there are a couple of commercial tools available, such as Octopai and Oracle Ent Metadata Manager. But they are pricey.