Very good sample of Procurement Spend – demo of Data Visualization (DV)/ Visual Analyzer (VA).
OBIEE 12c gives you the capability of working with statistical and R functions right from the ‘Edit formula’ pane. While I have found that this new feature was still not very user friendly, it’s a lot easier than making this functionality work in 11g. For example, to create a simple Trendline with 11g, the developer had to slowly build each step of a calculation to find the slope of a line, and then find the Y intercept. With these answers in hand, the results had to be carefully placed on a graph, so that it could render meaningful results. If you require statistical graphs within OBIEE, 12c may be a great fit for you. For example, below is a graph showing four different Trendlines:
The Criteria for building these four lines would be very intense in 11g; but in OBIEE 12c, it contained only five columns: one for the Calendar Year, and one for each Trendline. The Trendlines were created one at a time, by inserting the new “Analytics” Function in the column’s formula (see below).
This is a dream come true to many of us, though it requires an optional data visualization license. With this new functionality, you are able to use OBIEE along with any excel spreadsheet (XSA) saved on your machine.
You can add a spreadsheet to OBIEE from two areas:
- When you are creating an analysis (in the Criteria tab, and then choosing to add data source as shown below), or
- By going to the Visual Analyzer Home Page.
As this blog focuses on Answers, I will review the first option here.
There are three possible ways of analyzing a spreadsheet in Answers. You either want to:
- Analyze the spreadsheet by itself, or
- Use attributes from the spreadsheet along with fact data from your enterprise system, or
- Use fact data from your spreadsheet along with attributes and facts from your enterprise system.
For options 2 and 3 to work properly, it is important that your joins are properly matched (watch your cardinalities!) from your spreadsheet to your enterprise data. Also, as usual, option 3 will only work along with another fact table when the two tables are joined to a conformed dimension. Cardinalities and conformed dimensions are items that we generally take for granted when working on front-end OBIEE, because these points have been carefully handled during RPD modeling. Since the spreadsheet modeling has to be done in the front end, special caution must be used when modeling them in order to avoid “exploded” results, or simply inaccurate results.
There is also a more intuitive abbreviation of the measures that are placed on a graph. In 11g, when you dragged an amount to an axis, you may recall that the numbers would show up exactly as the raw number. So, if your result was 12,000,000, then that was exactly what you would see on the graph to begin. If you wanted to improve your graph, then you needed to go to the Graph Properties and format the data from the axis to be abbreviated into, for our example above, millions (or 12M). To save you a step, 12c will automatically abbreviate your graph data in the most user-friendly way. So, if the data is 12,000,000, you automatically get 12M!
Easy to use heat matrix!—I mean it: easy. While in 11g, you would have to be somewhat visually savvy and spend a lot of time conditionally formatting. OBIEE 12c gives you a tool that allows you to create a meaningful heat matrix in a matter of minutes—wait—even seconds. All you need is to know the two dimensions and one measure that you would like to use, and drag and drop them. Choose from an array of color schemas and how you would like to use the colors. In no time, your heat matrix is ready.
A new member of the OBIEE family is here to provide a visual solution for very complex activities. The Treemap provides a hierarchical structure that allows you to quickly spot patterns and outliers. At first, it may require a bit of head twisting to look at a graph like this, but remember, this is indeed a graph for complex activities. One of the most ideal usages for this new feature is the grouping by parent/children groups and the displaying of how two measures fair up inside each group.
If you’ve created lots of percent variance columns, it’s probably second nature that you will create your formula and then multiply by 100. In 12c, you can create your percent calculation without multiplying it by 100, then set your % data formatting in the Column Properties. In the same spot where you specify how the data is displayed, you can check the x100 box, which in turn will automatically multiply your results from that column by 100. Pretty sleek solution to simplify your formulas.
This feature is very well described here, so I will give a high level overview: 12c gives you a very easy way to save a complex formula into the catalog. If you’ve built a lot of logic in a column’s formula, and would like to reuse the logic in future reports, you will appreciate the opportunity of saving columns. I remember creating many financial calculations that had to be reused often, and until now there was no easy way to retrieve the column formulas. Trying to simplify my life, I ended up inventing “my own method” of saving complex calculations by saving different analyses that I named as “Master – Calculation” containing the columns that I reused often. I would start many reports based on these Master reports because they had my pre-built formulas; however, this was not a clean method for others to follow. OBIEE 12c gives you this clean and simple method for storing and reusing your most wanted columns. You do this by entering your formula in edit formula and choosing to “Save Column as” for future use.
OBIEE 12c provides a more intuitive way to create calculated columns than previous versions. In 10g or 11g, you needed to add a “whatever” column to the query, and then go in Edit Formula to define the calculation for your new column. While this worked, most new users often wondered why they were “bringing in two revenue columns,” for example. In 12c, you can add only the needed columns to your Criteria, then go straight to Results. In the Results tab, there is a New Calculated Measure icon that brings you immediately to the Edit Formula screen where you can name your new measure and define its formula.
In the real world, the migration between two OBIEE environments is really take time .The last time we did this for a customer migrating from 10g to 11g, we had to spend roughly 2 weeks just running through the dashboards clicking on all objects to do the 1st pass of the upgrade. When a customer asked us to migrate from 11g to 12c we were bracing ourselves for a similar ordeal. But the folks at Oracle have really done a great job this time with the Baseline Validation Tool (BVT). It allowed us to very quickly do a 1st pass through all the catalog objects and do a quick sanity check of the catalog and RPD after the upgrade.
The challenge is you need to make sure the content (analysis, catalog object, charts…) in two environments is same after the migration. BVT is FREE tool that enables the creation of a baseline set of data that can be compared with data from another system. While we used it for the upgrade it can be used in any situation where we need to run through 2 environments
- May be the result of an upgrade, patch or migration, or any change
- Automated regression testing on any two compatible Oracle BIEE environments
BVT as a tool is quit well thought thru and allows for several checks to be done in an automated manner.
Data Validation: Allows to check the numbers in the reports between the baseline and new environment.
- Check on actual data values exported from OBI
- Exported to CSV format
- Comparison report generated
BVT even downloads the data from the report as excel or CSV and it can be used to analyze the differences later.
Visual Verification: The purpose of this is to make sure that visually the 2 environments look the same. During the launch they spoke about using some sophisticated analytics to do the comparison, but whatever they have done it does pretty good job to compare the outputs. BVT generates reports for both analyses and dashboards
- Comparison report allows quick scan of (many) results in addition to a threshold test
- Use in conjunction with data validation
- Can be used to check various prompt combinations.
The tool checks all the reports and dashboards in the catalog and produces a report which can even be shared with the customer.
The output is exported and can be manually checked later of passed to the customer as evidence.
BI Catalog Verification: Compares the metadata of catalog objects (rather than objects themselves)
Logical Query validation: Used to validate the logical query between the 2 environments. Used in conjuction with the data and visual validation gives a complete picture of the 2 environments.
The logical query query is captured as evidence and for the failed ones can be used to determine the reason for the failure. Using the logical query in conjuction with the data and visual verification gives us complete confidence on the outcome of the testing.
What is the difference between a data warehouse architect and a data architect? How about BI solution architect?
Both data architect and data warehouse architect do data modelling, as in ERWin stuff. Or Embarcadero ER Studio. But a data warehouse architect is more specialised on designing the data model for a data warehouse, whereas a data architect is more specialised on designing the data model for databases used by transactional systems.
A data warehouse architect does a lot more than just data modelling. They also does the ETL and the infrastructure. These are areas that a data architect doesn’t do normally.
For a data architect to be able to call themselves a data warehouse architect, they don’t only need to learn how to create a data model for a data warehouse (as in dimensional modelling). But they need to also understand the ETL architecture. And they need to understand the servers. For example, they need to be able to specify the specification for the production data warehouse servers, i.e. CPU, memory, disks. And other server stuff like clustering, mirroring and DR. And they need to understand physical database stuff too, like table partitioning, file groups and materialised views.
In my book I specify there are 2 sides of data warehouse architecture. The first one is the logical architecture and the second one is physical architecture. A warehouse architect needs to understand both.
A data warehouse architect in my opinion demands 4 separate skills: data architecture, ETL, database platform and physical infrastructure. By “database platform” I mean SQL Server knowledge, Oracle knowledge, Teradata knowledge, Netezza knowledge, etc. For example, “SQL Server 2008 Data Warehousing Features” is a “must know” for a DW architect in SQL Server. Whereas UPI & USI is a “must know” for DW architect in Teradata. If we design a DW on Oracle 11g R2, we need to know Oracle specific DW features, such as Initialisation Parameter Settings, Partitionwise Joins, Compression, Parallel Execution, etc.
A BI architect is more on the application side, as in SharePoint architecture, Hyperion architecture, Reporting Services architecture, and Analysis Services architecture. For example: a BI system where we have reporting services running on SharePoint, plus excel services and PPS services on SharePoint, and SSAS cubes too reading from a warehouse or mart. And on top of that some custom .NET coding for authentication or customised security. Plus they allow some self-service BI using Qlikview or PowerPivot.
Back to the data warehouse architect, the ETL aspect of the job is sometimes quite demanding. There is something called ETL architecture in warehousing, which is basically
a) the architecture of the overnight batch, i.e. the structure of the tasks and workflows, the execution order of the workflows, the backup, the reconciliation, the checking and alert, and the data quality. The overnight batch is not only about data loading / ETL. It also has: data serving elements, i.e. processing reports (stored as PDFs, ready to be served to achieve split second response time), refreshing OLAP cubes.
b) the architecture of the continuous feed throughout the day for real time warehousing
c) the physical infrastructure of the ETL, i.e. the servers, the databases, the data connections
d) the methods to extract and load the data i.e. sliding window, swap partition, flipping twin tables, identifying incremental extraction, changed data capture, change tracking mechanism, (filtered) replication between warehouse and mart (or mirroring), how to extract data from cubes.
If a data warehouse architect only understands dimensional modelling, the company will have problems in the ETL and infrastructure. The servers might not be ‘suit for purpose’, for example disk configuration is not optimised for warehousing.
How about “BI solution architect”? A data warehouse is the back end. Business Intelligence is the front end. Data warehousing is about the data model, the ETL and the databases. BI is about the reports, the OLAP cubes, the analytical applications, the data mining, the KPIs, the dashboards, the score cards, and the performance management. It is a common perception that a BI solution architect is a “front end” person. An application person. This perception is incorrect.
A “solution architect” is responsible for the whole solution. Not only the front end, but also the back end. It is impossible for him to be responsible for the whole solution without looking after the back end. In the case of a BI solution architect, he or she needs to look after both the BI front end (reports, cubes, performance management, etc.), and the back end (data warehouse, ETL). It is impossible for a BI solution architect to be responsible for the BI without looking after the data warehouse. In some companies, instead of calling the role “BI solution architect”, it is called “data warehouse solution architect”. It doesn’t mean that the role is only responsible for the back end data warehouse, but he is also responsible for the BI front end. In some companies, they have not only one but several BI solution architects, each responsible for a certain area.
There are several different types of architects in IT, for example: system architect, data architect, solution architect, information architect and enterprise architect. This segregation of duties only happens in very large group of companies (enterprises). For example, a banking group with 3000 IT staff. If the IT staff is only 100 usually the architect does multiple functions. A system architect is responsible for infrastructure, including networks and servers. I’ve mentioned about data architect and solution architect. An information architect is responsible for the flow of information throughout the enterprise, including databases and data quality. An enterprise architect is responsible for all the applications in the enterprise, making sure they run in sync and adhere to the standards.
Need to make sure that the JDK 8 is already installed. If not then you need to download it and install.
Download the OBIEE 12c softwares and unzip it.
The first step is to install the Fusion Middleware 12c Infrastructure.
To start the installtion use the following command:
[oracle@apps12c OBIEE12c]$ java -d64 -jar fmw_184.108.40.206.0_infrastructure.jar
Welcome screen will appear. Just click Next to continue.
The next screen asks if you want auto-updates. Select Skip Auto Updates and click Next.
Next you need to specify the home location for Fusion Middleware, e.g. /apps/biee12c
The next screen asks if you want Middleware installed with examples. Select Fusion Middleware Infrastructure and click Next.
The installer will then perform prerequisite checks. Click Next.
Next screen, uncheck the check box and click Next.
Now the installation is ready to begin.
Click Install and let it run through. It does not take long time.
Once finished, you should see the summary screen. Click Finish to close the windows.