Rittman Mead Consulting
It’s the Sunday before the week of the BIWA Summit 2014, San Francisco, and Rittman Mead will be presenting a number of sessions and hands-on labs during the event. We’re also running a social event on the Wednesday night (I may even get the decks out again…), and of course there’s a great speaker line-up across the Oracle BI, analytics, data warehousing and data integration space.
The Rittman Mead sessions we’re presenting, and the date and time of the social event, are as follows:
Tuesday, Jan 14th 2014
- “Oracle BI Multi-user Development: MDS XML versus MUDE” : Stewart Bryson, 10:00 a.m. – Room 1
- “Deep Dive into BI Apps 11g and ODI” : Mark Rittman, 11:15 a.m. – Room 1
- “OBIEE, Hadoop and Big Data Analysis” : Mark Rittman, 2:30 p.m. – Room 1
- “Deploying OBIEE in the Cloud – Options and Deployment Scenarios” : Mark Rittman, 3:45 p.m. – Room 1
- Rittman Mead Social Hour – 6pm – 7pm
Thursday, Jan 16th 2014
- Exalytics Test Drive : Stewart Bryson, 8:45 a.m.
We’ll post the presentations on our website after the event, but if you’re going to be at BIWA Summit 2014 – we’ll see you there.
In the last blog I went through a very basic customisation, adding a new column to an existing dimension table. That served as an overview for how the BI Apps mappings are packaged in ODI for use in a dynamic ETL. This blog will go through the creation of a dimension and fact table, illustrating some of the concepts used to maintain data integrity. Most of these are similar to concepts used in previous releases of BI Apps, but modified for use in the new tool. For this example I will be adding two new tables to an existing EBS solution and running it as a single ETL load. The first is a dimension based on RETURN_REASON, the second is a fact based on SALES_RETURNS.
The first step is to create the source and target table definitions in the ODI model if they don’t already exist. Remember that you can just specify the table name and then use the Reverse Engineer feature to get the columns. The only constraint is that the table definitions are made in the correct models, but it’s worth grouping them into sub-models so that they can be navigated easily.
There are sample tables seeded in the repository for dimensions, staging tables and facts. These tables indicate the recommended naming convention (prefixing with WC_ instead of W_) as well as required system columns for warehouse tables. Below is a screenshot of the columns from the sample dimension table. All of these were included in tables for previous releases of BI Apps.
- ROW_WID: Surrogate key for dimension tables.
- INTEGRATION_ID: Natural key from the source table. Is often a concatenation of keys when several tables are used to populate a dimension.
- DATASOURCE_NUM_ID: The identifier of the source system the data was extracted from. This allows for multiple sources to populate the same warehouse table without conflict.
- ETL_PROC_WID: Run identifier for the load.
- EFFECTIVE_FROM_DT/EFFECTIVE_TO_DT: These can be used to enable SCD type 2 dimensions.
- CREATED_ON_DT/CHANGED_ON_DT: These dates (and all of the auxiliary changed dates) are from system columns on the source system. These are used to extract only newly changed information. The auxiliary dates can be used to improve this logic to derive from several tables.
In addition to the table and column definitions, some other attributes need to be configured in order for the load plan generator (LPG) to calculate the dependencies. The only data the user gives the LPG are the fact groups to load. From then, the following logic is used to generate the plan:
- Flexfield OBI Fact Group can be set on fact tables to link them to configuration groups.
- Staging tables are identified from the naming convention, e.g. XX_D will assume a staging table of XX_DS.
- Required dimensions for a fact are identified by reference constraints defined in the ODI model.
So for my example, I needed to set the fact group flexfield on the fact table as well as the constraints between the foreign keys and the newly created dimension table.
There is a fact group X_CUSTOM_FG which is included in each functional area. It is recommended that generic customisations are included in this group. You can set this on the datastore definition as above. In addition to this create various constraints on the new datastores.
- Staging Tables: Primary Key over INTEGRATION_ID and DATASOURCE_NUM_ID
- Dimension Tables:
- Primary Key over ROW_WID
- Alternate Key over INTEGRATION_ID and DATASOURCE_NUM_ID
- Fact Tables:
- Primary Key over ROW_WID
- Alternate Key over INTEGRATION_ID and DATASOURCE_NUM_ID
- References for each foreign key to the ROW_WID of the parent table
After the datastores are configured, it’s time to create the SDE interfaces and packages. Create these in the Custom_SDE folder as well so it’s separate from any prebuilt logic. Most of the columns can map across directly but it is important to use the global variable for DATASOURCE_NUM_ID. Variables are referenced by prefixing with # but also can be inserted using the GUI expression editor.
The other important thing for the SDE mappings is to add a filter for the extract logic. Previously, this was done using two workflows and overriding the logic on one of them. Now we only need one interface as we can use a global function (seeded in the repository) to perform the logic. The logic used in the example is as follows:
RUN_FULL_INCREMENTAL("#IS_INCREMENTAL",(RETURN_REASON.CREATION_DATE > TO_DATE_VAR('#INITIAL_EXTRACT_DATE')),(RETURN_REASON.LAST_UPDATE_DATE > TO_DATE_VAR('#LAST_EXTRACT_DATE')))
where #IS_INCREMENTAL is derived from querying a system table: W_ETL_LOAD_DATES. Once the mappings are made, they should be included in a package which refreshes the IS_INCREMENTAL and LAST_EXTRACT_DATE variables first. This is typical of all the extract mappings and can be made by just dragging the necessary objects across, defining one of the variables as the first step and joining them using the green connectors. For all staging mappings, choose the BI Apps Control Append IKM in the flow tab of the interface designer. There are BI Apps versions of all the default IKMs which have some additional features.
The SIL mappings are created in much the same way, but require an update key to be selected. It’s important that this is the key defined over the INTEGRATION_ID and DATASOURCE_NUM_ID. The update key can be set on the target datastore properties. In order to populate the ROW_WID, a sequence needs to be created. The prebuilt mappings all use native sequences stored in the data warehouse. This can then be imported into ODI and referenced by using NEXTVAL(SEQUENCE_NAME).
The other main difference for the SIL mappings is that they use the BI Apps Incremental Update or BI Apps Slowly Changing Dimension IKMs. For dimensions, the IKM has a useful option to insert the unspecified row automatically. For fact mappings (and some dimensions) it will be necessary to perform lookups. This procedure is done very simply by clicking the magnifying glass icon in the interface designer. That will open a wizard which allows you to select the table and the join condition. After that, any column from the lookup table can be used in the target expressions.
The SIL interfaces also need to be put into packages although only the IS_INCREMENTAL variable is required for refresh. Once all of the packages have been created, scenarios need to be generated for each of them. This can be done for each package at once by choosing generate scenarios at a higher folder level. Existing packages will be regenerated. These new scenarios need to be added to the master load plan, in the X_CUSTOM plans for SDE Dims, SDE Facts, SIL Dims and SIL Facts. Add the step by selecting the highest level and choosing Run Scenario step for the add menu. Then set the restart mode to Restart from failed step.
Once all of this has been done, the load plan must be edited to include the X_CUSTOM_FG fact group. This is done through the configuration manager where the plan can also be regenerated. After running the plan, I could see all of the tasks being included in the appropriate order. The data was successfully loaded into the fact table, with the foreign keys resolving.
That concludes the guide to customisations in the new BI Apps. Hopefully it was helpful with the overall process of how to do these customisations and why some of the steps are necessary. The Oracle documentation is very thorough and is certainly worth a look for some of the finer details. A lot is in common conceptually to previous BI Apps releases, the only step is the new tool which gives some very good new features.
Over the last couple of days I’ve been taking a look into the new BI Apps package Oracle have released using ODI instead of Informatica. Mark has already published an article outlining how ODI is used to manage and run the ETL process. However, this blog will focus on how you can make your own customisations in ODI and relate them back to concepts from previous BI Apps releases. If you want to follow along with the examples in this blog, I began by installing the applications using Mark Rittman and Kevin McGinley’s cookbook. This will take you through the point of generating a load plan to load one or more facts I won’t repeat the steps for this configuration, but will go through how to generate the load plan to include your custom packages. The fact group that I am selecting to load is Inventory Transactions (INVTRX_FG).
The most basic and typical type of customisation is simply adding a column to an existing table, called a Category 1 change. For this, I’ll go through a very simple addition onto W_INVENTORY_PRODUCT_D, just adding a new column to hold the current date. The first step required is to create some new sub folders to hold these custom mappings. This mirrors previous versions and is done for the same reason: to separate customisations from prebuilt metadata in order to allow for an easier upgrade path. This can be done in the Designer tab, using the Projects pane.
It is also recommended to edit the release tags for the new folders to register them to the correct BI Apps sources and targets. These tags allow for shortcuts to be made in ODI, and all of the objects relating to specific tags to be referenced together. You can edit release tags by clicking on the icon in the top right hand side of the Designer tab.
Next, find the interface (akin to an Informatica mapping) to be customised in it’s relevant extract folder. In this case I’m using EBS 12.1.3 as a source and modifying SDE_ORA_InventoryProductDimension. Copy the whole subfolder, that way you get the interfaces as well as the packages (similar to an Informatica workflow). At this point I added the custom column, X_CURRENT_DATE, to the database tables:
It’s still worth prefixing new columns with “X_” to denote customisation. ODI has the capability to import these changes into the mode, similarly to importing source and target definitions in Informatica. Open up the Models pane on the left hand side. This contains all of the table definitions for all schemas and is organised by source and then by table type.
After opening a table definition you can edit several attributes including the column definitions. The easiest way to do this is to use the Reverse-Engineer functionality. This will read the table definition from the database and import it into ODI. Another interesting feature of ODI is to choose the OLAP type. This has options of Fact, Dimension (SCD 1) and Slowly Changing Dimension (SCD 2). When set to Slowly Changing, you can edit the column properties to set their update behaviour. This way you can very easily alter a dimension to be SCD type 2 or vice versa.
Once the table definition has been saved, the new column can be referenced when editing interfaces. The process of adding new columns is relatively simple in that you can drag across the desired column into the target datastore. Furthermore you can use expressions which reference variables and functions defined in ODI. In this example I’ve simply set the new column to be CURRENT_DATE in the extract (SDE) interface. Then this column can then be brought through the load (SIL) interface. Often, the BI Apps interfaces will use Yellow interfaces (as indicated by their icon) as their sources. This is an ODI mapping which doesn’t load into a defined datastore. Instead you define the target columns in the interface itself and ODI will create a temporary table. This interface can be used as a source in another mapping. This can be chained as many times as necessary and hence can replicate flow-based mappings which were frequent in Informatica. Typically, they are used for similar purposes to a source qualifier in previous releases.
The interface is run as part of a package which can include other steps using functionality from ODI, the database or on the OS itself. This is equivalent to a workflow in Informatica. One of the key differences however, is that there is only one package required for both full and incremental loads whereas we had two Informatica mappings. This is because of the existence of functions and variables defined globally in ODI, whereas previously parameters were defined at a mapping and workflow level. The mechanics of this will be described in part 2 of this blog series. The package for the interface is accessible from the Projects pane as well.
The next step is to right click on the package and generate a scenario which will be executed by the load plan. Note, that before doing this, it is worth changing the user parameter Scenario Naming Convention to %FOLDER_NAME(2)%_%OBJECT_NAME%. This will ensure they match the out of the box scenarios. The final step is to ensure that the new mappings will be included in the generated load plan. As part of the configuration for BI Apps, you are asked to select which fact groups to include in the generated load plan. This is equivalent to adding subject areas to an execution plan and then generating the dependencies. This version of BI Apps has provided similar functionality through it’s Load Plan generator. The mechanics of the load plan generator will be described further in the next part of the blog. In order for the generator to pick up the new mappings, they need to be added to the master plan which is a superset containing all interfaces without any particular order. The master plan can be edited in the Load Plans and Scenarios pane of the Designer tab. The master plan information is under BIAPPS Load Plan/Load Plan Dev Components. They are split into the three extract phases and then subsequently split into fact and dimension groups. In this case, I’ve edited the INVPROD_DIM Load Plans for the SDE and SIL folders. Open the load plan and navigate to the steps section. Here we can modify the relevant task to use the scenario from our custom folder. This is the same as changing the logical folder for a task in DAC.
Now you can go back to the BI Apps Configuration Manager, navigate to Load Plans and regenerate the plan. This will include the custom scenario instead and you can reset data sources and run the plan to load the custom column.
In the next part of the blog I will go through how to do a category 2 customisation, creating a new dimension and fact table and adding that to the load plan.
It’s January 2014, and it’s that time of year when we start planning out this year’s BI Forum, which like last year’s event will be running in May 2014 in Brighton and Atlanta. This will be our sixth annual event, and as with previous year’s the most important part is the content – and as such the Call for Papers for BI Forum 2014 is now open, running through to January 31st 2014.
If you’ve not been to one of our BI Forum events in past years, the Rittman Mead BI Forum is all about Oracle Business Intelligence, and the technologies and techniques that surround it – data warehousing, data analysis, big data, unstructured data analysis, OLAP analysis and this year – in-memory analytics. Each year we select around ten speakers for Brighton, and ten for Atlanta, along with keynote speakers and a masterclass session, with speaker choices driven by attendee votes at the end of January, and editorial input from myself, Jon Mead and Stewart Bryson.
Last year we had sessions on OBIEE internals and new features, OBIEE visualisations and data analysis, OBIEE and “big data”, along with sessions on Endeca, Exalytics, Exadata, Essbase and anything else that starts with an “E”. This year we’re continuing the theme, but are particularly looking for sessions on what’s hot this year and next – integration with unstructured and big data sources, use of engineered systems and in-memory analysis, advanced and innovative data visualisations, cloud deployment and analytics, and anything that “pushes the envelope” around Oracle BI, data warehousing and analytics.
The Call for Papers entry form is here, and we’re looking for speakers for Brighton, Atlanta, or both venues. We’re also looking for presenters for ten-minute “TED”-style sessions, and any ideas you might have for keynote speakers, send them directly to me at firstname.lastname@example.org. Other than that – have a think about abstract ideas now, and make sure you get them in by January 31st 2014.
I’m just finishing off my Christmas and New Year leave but tomorrow I’m taking part in a webcast recording with Oracle’s Data Integration product management team, on Rittman Mead’s experiences with ODI12c over the beta program and since general availability. You should be able to see the video when the event takes place on January 14th 2014, but I thought it’d be interesting to note down some of our thoughts, particularly from Jérôme Françoisse who did most of our beta testing in EMEA and wrote two blog posts – here and here – on ODI12c’s new features when the product came out.
As Stewart Bryson said in his blog post “My Journey to ODI12c”, probably the things we liked most about Oracle Warehouse Builder were the flow-based mappings, and the Oracle Database-optimed code it generated. For anybody moving from hand-written SQL and PL/SQL code to a graphical ETL tool, multi-step operator-based mappings weren’t too difficult to understand and they fitted our general approach to building processes out of components and data flows. ODI’s approach of using single source-to-target transformations, together with these strange things called “knowledge modules”, translated well to individual SQL statements but meant more complicated ETL processes had to be developed as lots of separate stages, often using cumbersome features such as temporary interfaces and interface datasets. Of course what we didn’t like about OWB was the complicated (and fragile) process around connectors, deployments, configurations and so forth, but the flow-based editor was the main feature we missed in ODI.
So when ODI12c introduced its own flow-based editor (as well as maintaining compatibility with the older ODI11g way of creating interfaces), we were really pleased. Jerome’s first post on 12c new features covers the new flow-based editor well, and we were also pleased to see net-new features such as in-mapping impact and lineage analysis, as shown in one of the screenshots below.
What this new mapper also gives us is the ability to reproduce almost any SQL query with ODI, giving us more control over the GROUP BY/HAVING aggregation clauses, better PIVOT/UNPIVOT abilities, better DISTINCT capabilities, and better control over set-based operations. As the ODI12c patches come through more operators have been added to the operator pallette, and it’s also nice now to be able to load more than one table at a time, using the multi-table insert feature – though there’s still no “table” component as we had with OWB, where we can define a target table on-the-fly in a mapping and then instantiate it in the database later on.
ODI12c also introduced a few new concepts that aren’t all that obvious when you first take a look at them. The first one we came across was “deployment specifications” – as Jerome said in his article, what these allow you to do is have more than one physical specification for your mapping (the new 12c word for interfaces), with one using an incremental load KM, for example, whilst the other using a bulk-load one. This is all about reusability, and simplifying your ETL code base – the one logical mapping from source systems to target drives both the initial data load, which might use SQL*Loader or another technology, and then can be used to do the incremental loads afterwards, without having to maintain two separate mappings and risk “code drift”.
On the subject of reusability, OWB for a while has had the concept of reusable mappings, and now ODI12c does. Similar in concept to procedures and packages in PL/SQL, reusable mappings provide an input and output and allow you to define a common process, which can then be dropped into another mapping. In practice we didn’t see these used much in OWB, so we’re not sure what the ODI12c take-up will be like, but this is what 11g temporary interfaces turn into when you upgrade to 12c, so you’ll certainly see them used in your projects.
ODI12c also introduces something called “component KMs”. Up until now, knowledge modules have effectively been scripts, using a substitution API to pull in table names, sources and so on and then running as “interpreted” code to move data around your system. Component KMs in contrast are “black box”, compiled integration pieces, seemingly brought over as part of the OWB integration piece that use the same approach (we assume) that OWB used for generating ETL code, and presumably were introduced to support migrations from OWB. We’re not sure where this one is going – one of the best features of ODI is the open nature of the standard knowledge modules so we hope that feature doesn’t get lost, but my take is that this is to support OWB migration use-cases though it might be a way of introducing more specialised integration features in the future.
(Update: see the comment from David Allen below where he explains a bit more about why component KMs were introduced).
Other stuff that interested us in the new release included the debugger that’s now within ODI12c Studio, as shown in the screenshot below. Our view is that this feature is a bit of a “work in progress”, but it’s handy to be able to set breakpoints and query uncommitted data from the target database using the agent within Studio.
Another very useful feature that’s potentially a bit obscure though, is “blueprints”. This is really to address high-volume/velocity ODI use-cases where the actual process of retrieving process steps from the ODI repository, and logging of step results, slows the ETL process down and creates a bottleneck. With session blueprints, the steps are instead cached on each agent, and ODI only logs information relevant to the log level, rather than logging everything then removing the bits that aren’t relevant for lesser logging.
Obviously there’s lots more in terms of new features we were impressed with, but other notable ones were change notification when opening a mapping where its underlying data stores had changed; in-session parallelism so steps within a KM could run in-parallel, if there were no dependencies or requirements for serialisation, and a more consistent setup and management process that used concepts from WebLogic and Fusion Middleware – though install itself was a bit less flexible as we can’t select which components we want to install; It’s either standalone (only for standalone agent) or Enterprise (with JEE agent, ODI Studio, SDK, …), which means that servers running headless have an un-needed install of ODI Studio, whilst developers have JEE components they don’t need in their install folders. Stewart raves about the new Fusion Middleware/WLST-based component management though, along with the new Data Integration Management Pack for Enterprise Manager 12c that got released just after 12c, so I’m looking forward to putting these things through their paces in the near future once the Christmas break is over.
Now one thing that Jerome didn’t cover in his initial posts, as the feature came out via a patch after ODI12c first came out, is the OWB to ODI migration utility. This is actually the second part of the OWB-to-ODI story, as 12c also included a feature where ODI can run OWB processes from within an ODI package, as detailed in this blog post from Oracle that also talks about the migration utility and two of who’s screenshots I’ve used below (command-line migration utility on the left, selecting OWB objects to run “in place” from within ODI on the right).
This means that you’ve effectively got two ways that you can work with OWB processes in ODI – you can run them “in-place” from within ODI, something you’d probably do if the routine works well and there’s no point converting it to ODI, or you can migrate them into ODI, converting them to ODI mappings and then running them as normal ODI processes. Not everything comes across at this point – OWB process flows are the most visible part that’s missing, and according to Stewart who’s just finishing up an article for OTN on the topic, there are still bits you need to complete manually after a migration, but all of the business logic for the mapping comes through, which is what you’re really after.
There’s still a few pieces we’ve not really had the chance to look at closely – better integration with GoldenGate is one of the standout pieces in this area, and as I mentioned before the new EM Management Pack for Data Integration sounds like it’ll be a good complement to the database and BI ones, with in Stewart’s words “complete drill-through from Database to ODI and back again”. More from us on this and other data integration-related topics as the new year unfolds.
My new article at Oracle Magazine is on Oracle BI Mobile App Designer, the new HTML5-based mobile BI tool for OBIEE 220.127.116.11.1 built on Oracle BI Publisher technology. In the article, I walk the reader through creating a simple Mobile App Designer App, then publish it to the Apps Library for use with iOS, Android, Blackberry and other HTML5-compatible mobile devices.
You can also read my “first look” post on BI Mobile App Designer from our blog when the feature first came out, and we’re also running a promotion where we’ll implement your first Mobile App Designer app within a week, including patching up your OBIEE 18.104.22.168 installation to the required 22.214.171.124.1 version. More details on the offer, and on BI Mobile App Designer in-general, are on this QuickStart Mobile Analytic Apps for OBIEE 11g with Rittman Mead data sheet.