Framework for managing a BI architecture using an Oracle environment
For those who work with BI (Business Intelligence) and with Oracle environment and would like to know most of the objects that are inside your Data Stage and Data Warehouse.
Although we used this modeling for the BI team, this analysis can also be useful for DBA’s or any other team that manages a database.
Obviously, this process can be improved and even add more details, as for example, we can see more easily what the dependencies between objects are (Package and Procedure, Procedure and Procedure, Procedure and Table, etc.). We haven’t created that vision yet, but it would be very useful when we have it.
The DW data modeling that I will show in this post, was created by a BI team in which I developed, and has data that is within the core of Oracle itself, and also some external data (CSV files that come from SharePoint). But I’m not going to talk about what data was stored in Sharepoint or the type of data, because it depends on each company. The entire logical and physical model was developed using Oracle’s Data Modeler software.
In the modeling we have the dimensions and facts we created to analyze the creations we made in BI and to be able to analyze several metrics that we can use use in this model, such as:
· How many tables were created in the database, also which table belongs to the owner
· How many owners were created in the database
· What is the disk size of the objects
· How many data is empty (we use -1 for that situation), we use a function for that
· How many data that it has at the source but in other tables don’t (we use -2 for that situation), we also use a function for that
· How many columns we have, which type they are
· How many job schedules we have
· How many jobs is executing at the same time as other job, which can be a problem
· The logs that were executed for every procedure and package, so we can see how many success we have, how many errors, how many procedures for each system or owner, etc
· How many functions were created in the database
· How many tables doesn’t have index
· And several other useful metrics for the BI team to manage and make a decision based on those data
So we can see the data warehouse data model as an imagem below:
In this diagram, all created dimension tables are started as “D_” and all fact tables are started as “F_”.
Since the diagram was developed for the Portuguese language, we can find two tables called D_DATA which is a date dimension, so it can be called D_DATE, and D_FERIADOS is a holidays dimension and can be called D_HOLIDAYS.
Two standard columns are added to all tables, “LINDATA” and “LINORIGEM”, the “LINDATA” column is the date the record was loaded into the table and the “LINORIGEM” column is the source that the data was loaded.
The tables “D_BI_ENVIRONMENTS” and “D_BI_SYSTEMS”, are tables that are not in the core of Oracle as I had mentioned previously, and are data that come from Sharepoint through CSV file (I already made a post showing how to perform this process). The rest of the dimensions and facts are data that come from Oracle itself.
For this diagram we have the DDL available, the logical and physical model that was developed in Oracle’s Data Modeler and the scripts generated in SQL language.
So, the scripts for this post can be found on my Github (@guimatheus92).
