BI & Warehousing
We’re looking for a talented technical writer to come and join us at Rittman Mead. You’ll be helping produce our industry-leading training material. We run both public and private courses, off the peg and bespoke, to consistently high levels of approval from those who attend.
You need to have excellent written English, with a particular skill for clear communication and a keen eye for detail in presentation. We run a lean team here and all material you produce must be “print-ready” without the need for further review and editing.
Technically, you must have solid knowledge of OBIEE and preferably ODI and BI Apps too, with additional products such as Endeca and GoldenGate a bonus. Experience of cloud technology and administration will help but is not mandatory. We use a variety of tools in preparing our training material, so you need to be able to adapt quickly to new software (hint: Powerpoint is not the best presentation tool ;-)).
The role is full time, and can be based remotely. If you are interested, please get in touch through this link. We’d like to see an example of your recent writing, such as a blog entry. If you’ve any questions, you can email me directly : robin dot moffatt at rittmanmead dot com (but to register your interest in the role, please do so via the link).
This is the final post in my series on Oracle BI Apps 22.214.171.124.1 and GoldenGate Integration. If you have been following along up to this point, we have the Source Dependent Data Store schema setup and ready to accept data from the OLTP source via replication, the GoldenGate installations are complete on both the source and target servers, and the GoldenGate parameter files are setup and ready to roll. Before the replication is started, an initial load of data from source to target must be performed.Initial Load
As I mentioned in my previous posts, I plan on performing the initial load of the SDS a slightly different way than described in the Oracle BI Applications documentation. Using the process straight out-of-the-box, we must schedule downtime for the source application, as we do not want to skip any transactions that occur during the processing of data from source to SDS target. With a slight customization to the OBIA-delivered scripts, we can ensure the initial load and replication startup will provide a contiguous flow of transactions to the SDS schema.Oracle BI Applications Scripts
As with the other set-up processes for the SDS schema and GoldenGate parameter files, there is an ODI Scenario available to execute that will generate the initial load scripts. In ODI Studio, browse to BI Apps Project > Components > SDS > Oracle > Copy OLTP to SDS. Expand Packages > Copy OLTP to SDS > Scenarios and you will find the Scenario “COPY_OLTP_TO_SDS Version 001″.
The Scenario calls an ODI Procedure named “Copy SDS Data”. When executed, it will generate a script with an insert statement for each target SDS table using a select over a database link to the OLTP source. The link must be manually created and specifically named DW_TO_OLTP, as the ODI Procedure has the dblink name hard-coded. This means that the link will need to be modified for each additional source, should there be multiple GoldenGate OLTP to SDS replication processes setup.
-Drop and recreate the database link. drop database link DW_TO_OLTP create database link DW_TO_OLTP connect to SYSADM identified by SYSADM using 'PSDEMO'; --test the link. select * from dual@DW_TO_OLTP;
The standard process would then be to execute the Scenario to generate the insert statements, schedule a downtime for the OLTP application, and run the initial load scripts. Rather than go through those steps, let’s take a look at how to eliminate the source system unavailability with a slight change to the code.Zero Downtime
The initial load process will be customized to use the Oracle database flashback query capability, selecting data from the transaction log as of a specific point-in-time, based on the source SCN (system change number). Before the initial load is run, the GoldenGate extract process will be started to capture any transactions that occur during the data load. Finally, the GoldenGate replicat process will be started when the initial load completes, after the initial load SCN, eliminating the chance to skip or duplicate transactions from the source.
To perform the customizations, I recommend copying the entire “Copy OLTP to SDS” folder and pasting it in a new location. I simply pasted it in the same folder as the original and renamed it “RM Copy OLTP to SDS”. One thing to note is that the Scenario will not be copied, since it must have a unique name throughout the work repository. We will generate the Scenario with a new name after we make our changes.
Open up the ODI Procedure “Copy SDS Data” from the copied directory. Click on the “Details” tab to review the steps. We will need to modify the step “Copy Data”, which generates the DML script to move data from source to target. A review of the code will show that it uses the dictionary views on the source server, across the database link, to get all table and column names that are to be included in the script. The construction of the insert statement is the bit of code we will need to modify, adding the Oracle database flashback query syntax.
... l_trunc_stmt := 'truncate table <$=jmodelSchema$>.' || col_rec.table_name; l_sql_stmt := 'INSERT /*+ APPEND */ INTO <$=jmodelSchema$>.' || col_rec.table_name || ' (' || rtrim(l_column_list, ', ') || ') ' || 'SELECT ' || rtrim(l_column_expr, ', ') || ' FROM ' || col_rec.table_name || '@<%=DBLinkName%> as of scn #INITIAL_LOAD_SCN'; ...
As you can see, #INITIAL_LOAD_SCN is a placeholder for an ODI Variable. I chose to use a variable to perform the refresh of the SCN from the source rather than hard-code the SCN value. I created the variable called INITIAL_LOAD_SCN and set the query on the Refreshing tab to execute from the data warehouse over the database link, capturing the current SCN from the source database.
The user setup to connect to the OLTP source will need to be granted the “select any dictionary” privilege, temporarily, in order to allow the select from V$DATABASE.
SQL> grant select any dictionary to SYSADM;
Now that the Variable is set and the Procedure code has been modified, we just need to put it all together in a Package and generate a Scenario. The Package “Copy OLTP to SDS” is already setup to call the Procedure “Copy SDS Data”, so we can simply add the ODI Variable as a refresh step at the beginning of the Package.
After saving the Package, we need to generate a Scenario to execute. When generating, be sure to set all Variables except for INITIAL_LOAD_SCN as Startup Variables, as their values will be set manually during the execution of the Scenario. Also, remember to provide a different name than the original Scenario.
All of the pieces are in place to kick-off the initial load of the Source Dependent Data Store and fire up the GoldenGate replication. Even though the goal is to have zero downtime for the OLTP application, it would be best if the process were completed during a “slow” period – when a minimal amount of transactions are being processed – if possible.
First, let’s get the GoldenGate extract and data pump processes running and capturing source transactions. On the source server, browse to the GoldenGate directory and run the GGSCI application. Ensure the Manager is running, and execute the “start extract” command for each of the processes that need to be kicked off.
Now that the transactions are flowing into the source trail and across the network to the target trail, we can execute the Scenario to generate the initial load script files. When executed, a prompt will appear, allowing you to enter the appropriate value for each variable. The script can be filtered down by a specific list of tables, if necessary, by adding a comma-delimited list to the TABLE_LIST variable. We’ll just use a wildcard value to generate the script for all tables. Other options are to generate a script file (Y or N) and to execute the DML during the execution of the Scenario (even though the Variable is named RUN_DDL). I have chosen to create a script file and run it manually.
The script, named “BIA_SDS_Copy_Data_<session_number>.sql”, will disable constraints, drop indexes, and truncate each table in the SDS prior to loading the data from the source system. After executing the copy data script, we will want to run the “BIA_SDS_Schema_Index_DDL_<session_number>.sql” script to recreate the indexes.
SQL> @BIA_SDS_Copy_Data_885500.sql SQL> @BIA_SDS_Schema_Index_DDL_880500.sql
One thing to note – in the SDS Copy Data script the primary key constraints are disabled for a more performant insert of the data. But, the SDS Schema Index DDL code is set to create the constraint via an alter table script, rather than enabling the existing constraints. To work around this bug, I opened the Copy Data script in SQL Developer, copied all of the lines that are set to disable the constraints, pasted them into a new window and switched the “disable” keyword to “enable” with a simple find and replace, and then executed the script against the SDS tables.
After copying the data and recreating the indexes in the SDS (and enabling the PK constraints), we can finally startup the replicat GoldenGate process on the target server. Again, login to GGSCI and ensure the Manager process is running. This time, when we start the process we will use the AfterCSN command, ensuring the replicat only picks up transactions from the trail file after the initial load SCN.
We now have our initial load of data to the SDS schema completed and GoldenGate replication started, all without any impact to the source OLTP application. The next time the Source Data Extract (SDE) Load Plan is executed, it will be just as if it were running directly against the source database – only faster – since it’s pulling data from the SDS schema on the same server.
Be on the lookout for more blog posts on OBIA 126.96.36.199.1 in the future. And if you need a fast-track to Oracle BI Applications implementation, feel free to drop us a line here at Rittman Mead at email@example.com.
Oracle BI Apps 188.8.131.52.1 – GoldenGate Integration
In the first part of this article, we discovered the new mappings and reusable mappings, the debugger and the support of OWB jobs in the freshly released ODI 12c. Let’s continue to cover the new features in ODI 12c with CAM, the new component KMs and some features to improve performances or ease of use.
WebLogic Management Framework
Another big change with this new version is the new WebLogic Management Framework used to manage the standalone agent in replacement of OPMN (Oracle Process Manager and Notification). Based on Weblogic Server, it brings a unified management of Fusion Middleware components and it provides a wizard for the configuration – the Fusion Middleware Configuration Wizzard.
Practically the standalone agent is now installed outside of the ODI installation, in a domain similar to WLS domains. The benefits of it, apart from the centralised management, is that it supports Weblogic Scripting Tool (WLST) and can be monitored through Enterprise Manage Fusion Middleware Control or Enterprise Manager Cloud Control. The standalone agent can be launched manually or through the Node Manager.
Component Knowledge Modules
Next to the standard KMs – now called Template KMs -, a new type of knowledge modules has been introduced in this release. Going one step further in the reusability, the Component KMs contain steps that are defined once and shared among several of them. Instead of being templates of code with some call to the Susbtitution API at runtime , these KMs are actually compiled libraries that will generate the code based on the components present in a mapping.
The Component KMs are provided out-of-the-box and are not editable. You can check the type of a KM in the physical tab of a mapping.
Knowledge Module Editor
Don’t worry, you can still import, edit and create Template KMs. You can now find them in the folder <ODI_HOME>/odi/sdk/xml-reference.
Good news, the KM Editor has been reviewed to avoid us to constantly switch from one tab to another. Now when the focus is set on a KM task, it is directly displayed in the Property Editor. In addition the command field supports syntax highlighting and auto-completion (!!). Options are also now directly managed from the KM editor and not from the Project tree.
With ODI 12c, it is now possible to have the extract tasks (LKMs) running in parallel. It’s actually done by default. If two sources are located in the same execution unit on the physical tab, they will run in parallel. If you want a sequential execution, you can drag and drop one of your units onto a blank area. A new execution unit will be created and ODI will choose in which order it will be loaded.
Thanks to this the execution time can be reduced, especially if your sources come from different dataservers.
In the topology, you can now define the number of threads per Session for your physical agents to avoid one session to get all the resources.
Parallel Target Table Load
With ODI 11g, if two interfaces loading data in the same datastore are executed at the same time or if the same interface is executed twice, you can face some problem. For instance a session might delete a worktable in which the other session wants to insert data.
This now belongs to the past. With 12c, a new “Use Unique Temporary Object Names” checkbox appears in the Physical tab of your mapping. If you select it, the worktables for every session will have a unique name. You are now sure that another session won’t delete it or insert other data.
I can hear a little voice : “Ok but what if the execution of the mapping fails? With these unique name, these tables won’t be deleted the next time we run the mapping.” Don’t worry, the ODI team thought about everything. A task in a KM can now be created under the section “Mapping Cleanup”. It will be executed at the end of the mapping even if it fails. That’s a nice feature that I will also use for my logging tasks!
“But eh, what if the agent crashes? These cleanup tasks won’t be executed anyway.” No worries… A new ODI Tool is now available : OdiRemoveTemporaryObjects. It runs every cleanup tasks that have not run normally and by default it is automatically called when a agent restarts. Little voice 0 – 2 ODI Team.
When doing real-time integration with ODI, you can have a lot of jobs running every few seconds or minutes. For each call of a scenario, the ODI 11g agent retrieves all the steps and tasks it should execute from the repository and write it synchronously in the logs before starting to execute anything. After the execution it deletes the logs that shouldn’t be kept when the log level is low. At the end of the day, even if you set the logs to a low level, you end up with a lot of redo logs and archive logs in your database.
Instead of retrieving the scenario for every execution, the ODI 12c agent now only retrieves a Session Blueprint once and keep it in the cache. As it is cached in the agent, there is no need to get anything from the repository if the job runs again a few minutes later. The agent also write – asynchronous – only the needed logs defined by the log level, as it only relies on the blueprint for it’s execution and not on the logs. The parameters related to blueprints are available in the definition of the physical agents in the topology.
In summary, thanks to the Session Blueprints the overhead of executing session is greatly reduced. The agent needs to retrieve less data from the repository, doesn’t insert-and-delete logs anymore and write it asynchronously. Isn’t it great?
Datastore Change Notification
This happened to me once again last week : we needed to rename a datastore column in ODI but we couldn’t because it was used as a target datastore in an interface. The solution is to remove the mapping expression for this column in every interface where the datastore is used as a target.
It will not happen anymore with 12c. Now, you can change it in the model and a change notification will be displayed in every mapping using this datastore. In the following example, I removed the LOAD_DATE column.
Ever faced this dilemma in 11g ? Giving the Master Repository password to everyone is not safe, but saving it in the Login Credentials is not better if a developer forgets to lock his screen when he is away from keyboard.
Once again, 12c is there to help you. You can now store your Login Credentials in a Wallet, itself protected by a password. One password to rule them all… and keep them safe!
Try it !
If you want to give a try to ODI 12c and follow the Getting Started Guide, you can download a Pre-Built Virtual Machine. It’s running on Oracle Enterprise Linux with an Oracle XE Database 11.2.0. Of course, you will need Virtual Box to run it.
See you soon
Needless to say I’m very exited about this release. Combining the greatness of the ODI architecture with the new flow-based paradigm and all the new features, ODI is now a very mature tool and the best-of-breed for Data Integration regardless of the source or the target technology.
Keep watching this blog, there are more posts to come about data integration! Stewart Bryson already announced in his last post he would talk about the ODI 12c JEE agent and Michael Rainey started with GoldenGate 12c New Features. Who will shoot next?
[Update] Peter Scott will also present ODI 12c in his session at the Bulgarian Oracle Users Group Autumn Conference on 22nd November 2013. Stewart and myself will also talk about it at RMOUG Training Days in February.
I won't be able to make it to this event, but I wish I *could* be there. I can't think of a better way to kick off a weekend!
Enterprise Manager 12c is the latest and greatest incarnation of Oracle’s toolset for Enterprise Management (the clue’s in the name), of which Cloud Control (neé Grid Control) is one of the key tools. It can be used for a unified overview of disparate services including servers, databases, and of course OBIEE. Monitoring and alerting is core to EM12c, as well as extremely flexible management of any problems that arise. EM12c also offers natively all of the control and configuration functionality for OBIEE that exists in its sibling that is installed with all OBIEE installations, Enterprise Manager Fusion Middleware Control.
On this blog Mark Rittman wrote in detail about the EM12c in a series of three posts (here, here and here), and Adam Seed gave a detailed example here of how to monitor OBIEE availability using a user login simulated through a “Web Transaction” test.
In this post and the next I am going to expand on what my colleagues have covered, looking firstly at how we can use (dare I say, leverage) Usage Tracking data to drive monitoring and alerting of important areas such as response time and dashboard error rates, and secondly the incorporation of existing JMeter-based performance tests into EM12c.Terminology
EM12c has a learning curve associated with it. Just as you wouldn’t expect to grok OBIEE in an instant and a comprehension of the basics of the BI Server, an RPD, Answers, and so on will aid a good implementation, the same is true for EM12c. Just like with OBIEE’s SampleApp, Oracle kindly provide a VirtualBox machine preinstalled & configured with EM12cR3 for you to test on. It’s on eDelivery, linked to from here, listed under Oracle VM Templates (x86 64 bit), Part Number B73896-01.
Within EM12c objects are defined as Targets. These include servers (known as hosts), databases, database listeners, but also components of parent targets. For example, the overall FMW Domain for OBIEE is a target, but so is the BI Server and each individual java application deployment running on the WebLogic Server managed server (bi_server1) which is itself also a target
The data for targets is provided by an Agent typically running local to the target. The data consists of various Metrics which are Collected on a periodic basis. The frequency of a metric collection can be configured, with a different Collection Schedule per Metric. One metric that most targets will provide is Status and this particular metric is collected every minute by default. Other metrics are collected less frequently, from every 15 minutes up to once a day or less.
Alerts within EM12c are primarily handled through Incidents, which are concisely defined as “[…a] situation or issue you need to act on”. Incidents are created from the occurence of one or more Events, which are basically something (not necessarily ‘bad’) that has been measured by EM12c and deemed of interest.
The reference manual gives a nice illustration of how events and incidents are related, here.
A target’s metrics trigger Events through Thresholds, which can be defined at both warning and critical levels if required. The Status metric mentioned above will have a default Critical Threshold of “Down”, which is how EM12c will raise an alert for a component that is not running.
All of the above is core functionality provided by EM12c and in a sense generic to any target. Each target will have its own metrics for which thresholds can be defined, whether this is the OBIEE Presentation Services “Connection Pool Current Authentications” or the deadlock rate in TimesTen.
To provide flexibility in the monitoring that EM12c provides there is the concept of a Service. A Service can have one or more Service Tests defined within it, and these can be a variety of functions that return metrics, such as availability or performance data. From these metrics, thresholds can be defined just as they are against metric data from a pre-defined target, and thus events and notifications driven. A service test can be of many types, including:
- Web Transaction (as used in Adam’s article here)
- SQL query (OCI or JDBC)
- Custom script
In the remainder of this post I am going to explain how to build an EM12c Service Test based on Usage Tracking data to monitor and alert on things like failing dashboards and dashboard response times.Usage Tracking
Usage Tracking is functionality available within OBIEE that logs every user request to the BI Server into a set of tables stored in the RCU (BIPLATFORM) tables. If you don’t have Usage Tracking enabled, you should – period. It’s easy to implement (see here for a step-by-step tutorial from Oracle), and provides a wealth of valuable data including when a report ran, which dashboard it was part of, how long it took, how many rows it returned, and even the query that was sent to the database to run it.
The information that Usage Tracking provides supports multiple purposes :
- Short term system monitoring
- Who has been using the system?
- What are the long-running reports?
- System usage profiling
- Which dashboards get used the most?
- Which dashboards never get run?
- Which users are the most active?
- When are the busiest times of day?
- Problem analysis
- Which reports have failed?
- What errors have been logged in report failures?
- Which reports are returning an excessive number of rows of data?
- Which reports are requiring excessive number of queries on the database (and thus adding load to the BI Server in federating the results)?
Usage Tracking consists of two tables,
S_NQ_ACCT for the “Logical” query from the user and
S_NQ_DB_ACCT for the “Physical” query (or queries) that get sent to the database. Both tables reside in the RCU (BIPLATFORM) schema and can be queried just as you would any table in the database. They can also be modelled into the OBIEE RPD and reported on through the front end, but that’s a separate topic (have a look at the superb SampleApp v309R2 for inspiration).
For this example, let us look at a simple query against the main
S_NQ_ACCT table, showing any dashboards that have failed (
SUCCESS_FLG !=0) in the past 30 minutes (
START_TS > SYSDATE - ( 30 / 1440 )):
SELECT A.USER_NAME "USER", A.SAW_DASHBOARD "DASHBOARD", A.SAW_DASHBOARD_PG "DASHBOARD PAGE", A.START_TS "EXECUTION TIMESTAMP", A.ERROR_TEXT, A.SUCCESS_FLG FROM S_NQ_ACCT A WHERE A.SUCCESS_FLG != 0 AND START_TS > SYSDATE - ( 30 / 1440 )
Knobbling the system to throw an error in OBIEE thus:
We can see the resulting data in Usage Tracking’s
So there’s the error, plain to see if you go looking for it. But – how do we get EM12c to do that for us? Integrating Usage Tracking into an EM12c Service
Wouldn’t it be useful if our Enterprise Management tool (EM12c) could monitor the rate at which errors were being received by users, and alert us if it goes above a certain rate? I certainly think so. This, and any other Usage Tracking query you care to craft, can be easily integrated into EM12c through the definition of a Service Test using the Oracle SQL Timing test type.
To start with you will create a new Generic Service which is going to be the container for this particular Service Test and any similar ones. Launch the creation process from the EM12c Setup menu in the top-right, then Add Target and Generic Service. I have defined my Generic Service as being called “OBIEE”, associated with an Exalytics system called EXA01. For non-Exalytics, just select the FMW domain (bifoundation_domain) of the system you’re monitoring.
Click Next and leave the Availability definition as “Service Test” and click Next again.
On the Service Test page, set Test Type to Oracle SQL Timing (it doesn’t matter that it’s called a “timing” test even though we’re more interested in the rows returned). Set the Collection frequency to be however often you want to run this check – but make sure the value you choose matches up with the timespan you specify in your SQL otherwise you’ll miss or double-count errors.
Specify your RCU database details and credentials, and then paste your SQL statement into the Query area. Note that whilst the query above brings back details of the failed analysis, the test we’re building is simply looking at the number of rows, so for the sake of brevity our SQL statement could actually just select a single column – it’s the predicate that matters.
Click Next to move to the Beacons definition page. A Beacon is a host from which the test will be executed. For the purposes of this type of test it doesn’t matter from where it runs, but other types of testing (including JMeter that I’ll talk about in the next post) it is very useful to be able to define multiple, geographically separated, execution hosts (“Beacons”). So for now, just click Add and select the pre-defined EM Management Beacon.
On the next screen, Performance Metrics, click the radio button next to the default Total Time (ms) and click Delete. Then with “Based on Service Test” selected in the menu click on Go
Select the metric Number of Rows Fetched and click Continue. Now you can set the desired thresholds for your new metric – how many analyses should fail in the time period (eg 30 minutes) should cause a Warning, and how many would be Critical?
Click Next and leave the Usage Metrics undefined. Click Next to go to the Review page and then Finish to create the service. If all has gone well you should now see your service listed along with a green arrow under “Key Tests” showing that the service test we just built (the only service test so far, therefore the Key Test by definition) is up.
Click on the name of your service and under Key Test summary you should see your service test listed.
Click on the name of it to see the data it has collected – if you’ve only just created it then most likely there will be no data shown. As with all new configurations and targets in EM12c, you have to practice a bit of patience for agents and the like to sync up, to hit their collection frequency, and so on. If you are feeling impatient, you can click on Verify Service Test to get to a screen from where you can manually run it (note that clicking this alone doesn’t run the service test yet).
On the next screen, click on Perform Test to actually run the service test. It should momentarily refresh the screen and show the results, including the number of rows fetched.
To validate the service test, generate some failures in OBIEE if you haven’t already (locking the datawarehouse DB account is a quick -if unsubtle- way to do this) and wait for the service test collection interval to come round. You should see an incident raised:
The great thing about defining a service test is that it keeps history, so you can track things like error rates over time. The service page gives an overview:
From the overview you can drill into detail for time periods:
You can also see the metric’s value against the warning and threshold settings:
Using the same Usage Tracking data but displayed through OBIEE, it is possible to see the actual failures and associated dashboards and users:
This is using the Usage Tracking RPD that comes with SampleApp v309R2, merged into the existing RPD running on my system. SampleApp also includes a comprehensive set of dashboards based on Usage Tracking data.Conclusion
So that wraps up my first post on this subject, demonstrating creating a custom service test to make use of the invaluable Usage Tracking data. Next, I’ll be looking at how we can integrate JMeter tests into EM12c for clever reuse of existing code and a simple way of tracking system performance.
There were partners in my session, and I had no problem at all with it. After all, this was a conference open to anyone and I am a firm believer that when people learn more, the whole community benefits. This is what motivates me to write all of my books, cause believe me, it's not for the money (Google "Starving Authors" before you ever think to make money writing). I also speak at way too many events around the world each year from tiny user groups to massive conferences like Kscope, Collaborate, and OpenWorld with no concerns that my sessions are primarily filled with Oracle partners looking to improve.
After my session was over, there was one more timeslot for the day and since I didn't want to sit in the hall for 90 minutes, I went to Huron Consulting Group's (they're the company that bought Blue Stone) session on the future of Planning. It sounded more interesting than Hackett's session which was my other possibility and I saw that one of the speakers was Mike Nader who is a great presenter. If nothing else, I would get to hear Mike's engaging take on the world since he joined Blue Stone. I sat in the room in the back row (there were plenty of extra seats, but I wanted to leave the good seats for potential customers).
Right as the session was about to start, Rick Schmitt from Huron (Blue Stone) came over to me and asked me to leave. I was curious why since I was an official attendee at the event and he said that they were going to be talking over "some proprietary stuff." I assumed he meant his slides at the beginning on the Blue Stone acquisition or "why Blue Stone is the best at XYZ," so I offered to leave for the first few slides. I don't need nor want competitive info and I certainly didn't want to make him nervous during his sales pitch. He said that no, they were going to share lots of proprietary info throughout the session and he didn't want competitors in the room during their session at all.
Rather embarrassed but more bemused, I smiled, gathered my things, and walked out of the room. I sat in the hall for a while wondering what cool things I was missing and feeling jealous of the 50 clients that got to hear from Blue Stone. (There's nothing like being excluded from something to make you want it more.) As I sat there, I pondered my own stance on information sharing. Personally, I believe that if the community as a whole gets better - if the community learns more - the quality of Hyperion implementations will rise. Satisfaction with Oracle EPM will rise, and as the reputation of Hyperion gets better, the Hyperion market will grow which benefits the entire community: customers, Oracle, and partners.
And it made me ask what I could be doing better.
So starting effective immediately, all of the public webcasts interRel does (and we did over 100 webcasts last year) will be open to everyone. That's right: competitors, please come join our webcasts and we'll share all the information that we spend months putting together with you. You've always had access to our books, our sessions at user groups, our presentations at conferences, and now you have access to our webcasts too. I hope that this starts a trend: I strongly encourage our competition to open up their sessions and webcasts to anyone who wants to attend. Don't be afraid: if you're good at what you do, you shouldn't be afraid to help the competition get better too. Information is meant to be free and to point out the obvious, if the Hyperion market gets bigger from happier clients telling everyone they know to buy Hyperion, your potential customer base gets bigger too.
Our next webcast is Thursday, October 31. It's on how Smart View is finally an awesome replacement for the Essbase Excel Add-In and I hope to see a ton of our competition on the webcast. Visit http://bit.ly/iRWebcasts to register.
That's really all you need isn't it?
Today wraps up the voting period for the ODTUG Board of Directors. If you're asking me what ODTUG is, stop reading now. If you are a member of ODTUG, then please give me a few minutes to pontificate (that's a word I heard Jeff Smith use once, hopefully it makes sense here).
Your favorite Oracle conference, KScope, is largely successful based on the efforts of the Board, along with the expert advice of the YCC group. In addition, if you think ODTUG should "do more with Essbase" or "charge more for memberships" these decisions are made and carried out by the board.
So if you like being in ODTUG, and you want to help it get better and grow, and be as awesome as possible, you only need to do one thing today. Go vote. Midnight tonight (10/29) is the deadline. Do it.
You get to vote for several people. I suggest you read their bios. I'll save you the time for at least one vote, and that's for Danny Bryant.
Besides that awesome photo (#kscope12 in San Antonio) up above, here are several more reasons.
1. He's into everything. OBIEE. EBS. Essbase. SQL Developer. Database. Not very many people have their hands in everything, he does. He will be able to represent the entire spectrum of ODTUG members.
2. He's a fantastic human being. It's not just because he takes pictures of himself wearing ORACLENERD gear everywhere (doesn't hurt though), he's just, awesome.
3. This (Part II)
4. He also always answers the phone, tweets, and emails I send him. He might be sick, or he might just be that responsive. The ODTUG Board member responsibilities will fit nicely on his shoulders I believe.
So go vote. Now.
This is the third post in the four part series on Oracle BI Applications 184.108.40.206.1 and Oracle GoldenGate 11g. Last time, we made it through the initial setup of GoldenGate and created the necessary schemas for the Source Dependent Data Store (SDS) and GoldenGate on the source and target. Now it’s time to configure Oracle Business Intelligence Applications (OBIA) to use the SDS schema, setup the tables in the SDS schema, and generate the GoldenGate parameter files.Enable the SDS Set the SDS Parameter
The first step in configuration is to enable the SDS in the OBIA Configuration Manager. This indicator, set for each source instance, will be used throughout the Oracle Data Integrator (ODI) ETL processing to determine whether to connect directly to the source or to the SDS. Choose “Manage Data Load Parameters” from the Tasks list. In the Manage Data Load Parameters window, select the appropriate Source Instance (in this example we are using PeopleSoft Campus Solutions – so PS_CS is chosen) and search for the Parameter name IS_SDS_DEPLOYED.
Select the parameter and click the Edit button. There will be a warning that this parameter will be set globally for this Source Instance. Since we plan to pull all of our data from Campus Solutions using OGG, click Yes to continue (otherwise, you could choose a specific fact group on which to set the parameter). In the Edit Dialog, change the parameter value to YES.
Now we need to create the Physical Schema for the SDS schema in the Oracle Data Integrator Topology metadata. Open ODI Studio and select the Topology Navigator. Under Physical Architecture, drill-down into Oracle and find the Data Server that corresponds to the data warehouse target for OBIA. Right-click and choose “New Physical Schema”. Set both the Schema and Work Schema values to the SDS database schema that we created in the previous post.
Next, click on the FlexFields tab. We need to set the DATASOURCE_NUM_ID FlexField to match that of the source instance for which the SDS schema is a target. If you recall from the previous post, the DataSource Number, or DSN, was set to 20 for my OBIA setup of the PeopleSoft Campus Solutions source instance. Set this value to the DSN and save the Physical Schema.How It Works…
It’s great that once the SDS parameter is set in the Configuration Manager and the SDS schema is created in ODI, both steps we just completed, the Load Plans will just “know” to extract data from the SDS rather than the source itself. But I like to dig a little deeper to find out what’s really going on behind the scenes – especially when it comes to Oracle Data Integrator.
Thinking about how I would implement this feature in ODI, I first started looking for an ODI Variable…and sure enough, I found one named IS_SDS_DEPLOYED. The Variable refresh code calls a procedure which accepts a DSN and returns a true or false. Next, I checked the Loading Knowledge Module used in the Interfaces that extract from PeopleSoft Campus Solutions, “LKM BIAPPS SQL to Oracle (Multi Transport)”, as this is where the connection to the source is made during execution. In the list of LKM steps is one titled “Get SDS Schema”. The code checks to ensure the IS_SDS_DEPLOYED variable is true, then runs a SQL query against the ODI repository to lookup the SDS schema name based on the DSN in the physical schema flexfield.
String sqlSelSDSSchema="SELECT PS.SCHEMA_NAME ,PS.I_PSCHEMA, PS.EXT_NAME , FFV.SHORT_TXT_VALUE " + " FROM SNP_PSCHEMA PS INNER JOIN SNP_FF_VALUEM FFV ON FFV.I_INSTANCE = PS.I_PSCHEMA " + " INNER JOIN SNP_FLEX_FIELD FF ON FF.FF_CODE=FFV.FF_CODE AND FF.I_OBJECTS=FFV.I_OBJECTS " + " INNER JOIN SNP_CONNECT C ON C.I_CONNECT =PS.I_CONNECT " + " WHERE FF.FF_CODE='DATASOURCE_NUM_ID' AND FF.I_OBJECTS=1500 " + " AND C.CON_NAME='" + tgtDataServerName + "' and FFV.SHORT_TXT_VALUE='" + "#DATASOURCE_NUM_ID" + "'";
The SDS schema is then used in the next step, “Create SDS work view”, to generate the C$ view code against the SDS schema rather than the source instance schema. A bit of Java pattern matching is used on the standard “odiRef.getFrom()” substitution API call to perform the replacement.
String fromClause=odiRef.getFrom(); String sdsSchema = "[_SDS_]"; Pattern pattern = Pattern.compile("(=snpRef.getObjectName([^,]*),\s*\x22([^,]*)\x22,([^\)]*)\))"); Matcher matcher = pattern.matcher(fromClause); String squote = new Character((char)34).toString(); String replacefromClause = matcher.replaceAll("out.print("+squote + sdsSchema +".$3 " + squote + ");"); //Replace for Qualify function since this runs on target pattern = Pattern.compile("(\bQUALIFY\(([^\)\s]*)\))"); matcher = pattern.matcher(replacefromClause); replacefromClause = matcher.replaceAll(sdsSchema +".$2 “); %> create or replace view <%=odiRef.getTable("L", "COLL_NAME", "A")%> as … from <%=replacefromClause.replace("[_SDS_]","<$=sdsSchemaName$>")%> …
It looks as though the variable is also used many other places, but now we know exactly how the ODI Interfaces used in the Load Plans for OBIA’s extract can so easily switch to using the Source Dependent Data Store. The customized code written in ODI specifically for the SDS is definitely worth a look.Generate and Execute SDS DDL Scripts
Now we have Oracle BI Apps configured to use the SDS schema as a source rather than the actual source. GoldenGate will keep the SDS schema up-to-date with the latest source changes in real-time. Next, we will execute an ODI Procedure to generate the DDL scripts for the SDS schema tables and indexes, as well as the grants on the source tables for the GoldenGate user. I like how the OBIA developers made this easy. The only way to make it even simpler would be to call this procedure from the Configuration Manager (maybe in the next release?).
To execute the Scenario, open ODI Studio, go to the Designer Navigator, and in the Projects browse to BI Apps Project > Components > SDS > Oracle > Generate SDS DDL. Expand Packages > Generate SDS DDL > Scenarios and you will find Scenario “GENERATE_SDS_DDL Version 001″.
Right-click the Scenario and select Execute. After selecting the Context and Agent, you will be presented with a list of ODI Variables used in the Scenario. These will provide input into the script generation Procedure and are described in detail in section 5.4.4 Setup Step: Generate, Deploy, and Populate the Source Dependent Schema Tables on Target Database of the OBIA documentation.
I’m generating the DDL script for the first time, so I will choose “FULL” for the REFRESH_MODE variable, which will attempt to perform a drop and recreate of all objects. I have also decided to run the scripts manually, so I set the CREATE_SCRIPT_FILE equal to “Y” and RUN_DDL to “N”. I could actually have the Scenario execution run the script for me by setting RUN_DDL equal to “Y”, which is a nice feature. After setting all variables appropriately, click OK to execute the Scenario. Browse to the temp directory specified to review the different scripts:
- BIA_SDS_Schema_Table_DDL_<session_no>.sql and BIA_SDS_Schema_Index_DDL_<session_no>.sql, which can now be executed against the SDS schema to create the SDS tables and apply indexes
- BIA_SDS_Schema_Source_Grants_DDL_<session_no>.sql, which can be run against the source schema to apply grants to the source GoldenGate user
The OBIA-GoldenGate documentation describes the setup and configuration of the initial load process next. But, since I plan on doing this a slightly different way, I’ll hold off on describing it until the next post.Generate GoldenGate Parameter Files and Perform Configuration Generate Parameter Files
Finally we’re at the point of generating the parameter files for the GoldenGate extract, pump, and replicat processes that we setup in the previous post. Here is where we might expect to see the “JKM Oracle to Oracle Consistent (OGG)” Journalizing Knowledge Module and the ODI CDC Framework put to use…but, that is not the case. For starters, the CDC Framework is not a part of the OBIA implementation for change data capture. OBIA developers did it a different way, by comparing the “Last Extract Date” to the “Last Update Date” for new and changed records, always pulling from the fully replicated table in the SDS. Next, they created an ODI Procedure to generate the parameter files rather than using a JKM.
Just as we did with the Generate DDL Script Scenario, browse to BI Apps Project > Components > SDS > Generate SDS OGG Param Files. Expand Packages > Generate SDS OGG Param Files > Scenarios and find Scenario “GENERATE_SDS_OGG_PARAM_FILES Version 001″. Right-click and select Execute. We are again provided with a list of Variables which need the appropriate values added prior to starting the process. Many of them should look familiar if you have worked with the GoldenGate JKM – but with much more intuitive names.
Set the appropriate values for the variables, described in detail in section 5.4.5 Setup Step: Generate and Deploy Oracle GoldenGate Parameter Files to Source and Target Machines of the OBIA docs, and click OK to execute the Scenario. Once the Scenario has completed, browse out to the temporary directory and you should find a folder DSN_<DATASOURCE_NUM_ID>. Within this directory are a source and target folder, and the generated parameter files within each. Copy the .prm files from the source folder to the source GoldenGate dirprm directory, and the ADD_TRANDATA.txt to the GoldenGate home directory. From the target folder, copy the parameter file to the target dirprm folder.GoldenGate Configuration
To begin our configuration, let’s first edit our extract, pump, and replicat parameter files to add any options or commands necessary, such as trail file encryption. This can be done using the local system text editor or via GGSCI (the GoldenGate Software Command Interpreter). If there are no special options to add to the parameter files, they will work just fine without any modification.
Next, we’ll add the table-level supplemental logging on the source by running the ADD_TRANDATA.txt script file. This script file is just a list of GoldenGate commands that can be executed as a batch by calling the obey command. Log-in to GGSCI and run the following:
GGSCI (pssrc) 3> obey ADD_TRANDATA.txt
Once completed, remember to revoke the ALTER TABLE privilege from the source GoldenGate user.
REVOKE ALTER ANY TABLE FROM ogg_user;
The final piece of configuration is to generate the source definitions file. This file provides the GoldenGate replicat process on the target the definitions of the source tables so it can interpret the table and column mappings in the replicat parameter file. The reason it must be used for replication to the SDS is because there are 3 additional columns mapped to each target table.
CDC$_RPL_LAST_UPDATE_DATE = @DATENOW (), CDC$_SRC_LAST_UPDATE_DATE = @GETENV ("GGHEADER", "COMMITTIMESTAMP"), CDC$_DML_CODE = "I" --"U" or "D"
The replicat last update date (CDC$_RPL_LAST_UPDATE_DATE) will be the timestamp when the record is updated in the target table. The source last update date (CDC$_SRC_LAST_UPDATE_DATE) is the timestamp when the change occurred in the source. And finally the DML code (CDC$_DML_CODE) represents the type of change; insert, update, or delete.
To generate the source definitions file, exit GGSCI and run the following command:
./defgen paramfile ./dirprm/DEFGEN.prm
Once completed, copy the source definitions file from the source server to the target server dirdef directory.
We’ve now configured the GoldenGate parameter files on both the source and target. In the next, and final, post in the series, I’ll discuss the initial load process and we will finally start the source to SDS GoldenGate replication.
Oracle BI Apps 220.127.116.11.1 – GoldenGate Integration
Most readers of this blog probably know me for my time spent with OBIEE (especially my work in repository development), but I’m guessing some of you long-time readers know that I grew up working with the Oracle Database. Many and many a year ago, I was a fledging Oracle DBA getting all my answers from the AskTom posts by Tom Kyte and the white papers by Carey Millsap. It wasn’t too long before I got my first chance architecting a data warehouse. I was hooked. Single-row lookups no longer interested me… it had to be millions. Then I started writing ETL… first using Perl (as a wrapper for generating SQL and executing it through DBI), and then later using PL/SQL. Finally, I got my hands on Oracle Warehouse Builder. The waters parted. I was home.
What did I like so much about OWB? It generated awesome code. It used smart Oracle loading techniques such as multi-table inserts, MERGE statements instead of UPDATE statements, partition-exchange loading, etc. It generated the kind of set-based, pure SQL that I had been wrapping in languages such as Perl and PL/SQL for years. What did I not like about OWB? Those times when the code wasn’t optimal, or the GUI simply didn’t allow me to do the things I wanted to do (SQL Analytics anyone?) I ended up writing logic in views, or crafting complex pre- and post- processes in PL/SQL, which ironically gave birth to the Transcend product that Rittman Mead offers today.
Then Oracle purchased Sunopsis and Oracle Data Integrator was born. At first… meh. There was a lot to like about ODI, I’ll grant you that. But it took me a while to get past the “different” user interface, and the confusing Topology. But once I wrapped my head around it… I found the yin to OWB’s yang. I couldn’t believe the power I had discovered in the Knowledge Module framework. ODI still generated set-based SQL code because of the similar EL-T approach as OWB, but I was now able to modify how the code was generated without stepping outside of the tool. Additionally… I started to understand and appreciate the Topology, drawing a comparison to the OBIEE sematic layer with the capability of abstracting the logical from the physical, and building more logic into the model instead of the processes that used it. But we were still left in a bit of a between-state. Oracle had two very good ETL products, but neither one felt complete. One tool gave me the standard “operator” paradigm and accompanying flow-based design. The other gave me the power to move mountains by affecting the generated SQL working directly at the source. If only Oracle could bridge the gap… and deliver one product that ticked all those boxes.
Ladies and gentleman… I give you ODI 12c.
One of the best parts of working for Rittman Mead is that I am constantly under NDA with Oracle and get to see new features, product roadmaps, and generally get an insider’s look at what’s coming in future releases. But I have to tell you… the ODI 12c release is one of the hardest secrets I’ve ever had to keep. Years ago, I was asked by Oracle (mostly by David Allan, I think, in retrospect) to participate in usability studies they were conducting on some of the early wireframes of what eventually became ODI 12c. The first time I saw the new flow-based design… all the metal pieces clicked into place for me. This is what we have been waiting for… and what David referred to when he recalled my demand to “just have a mapper”.
So I know you guys want some technical posts form me on ODI. Don’t worry… they’re coming. I won’t try to duplicate the excellent post by Jerome Francoisse delineating the new features. The first one I’ve already started writing is on JEE agents in 12c… if I can get it done before Michael Rainey or Mark Rittman beat me to it. I also want to spend some time with the new Component KM’s, a feature which I think is on equal footing with the flow-based design. So keep your eyes peeled, watch the blog for new ODI content, and let’s enjoy the victory.
Back in the 10g release, if you wanted something beyond the standard query for your report extract; you needed to break out your favorite text editor. You gotta love 'vi' and hate emacs, am I right? And get to building a data template, they were/are lovely to write, such fun ... not! Its not fun writing them by hand but, you do get to do some cool stuff around the data extract including dynamic SQL. By that I mean the ability to add content dynamically to your your query at runtime.
With 11g, we spoiled you with a visual builder, no more vi or notepad sessions, a friendly drag and drop interface allowing you to build hierarchical data sets, calculated columns, summary columns, etc. You can still create the dynamic SQL statements, its not so well documented right now, in lieu of doc updates here's the skinny.
If you check out the 10g process to create dynamic sql in the docs. You need to create a data trigger function where you assign the dynamic sql to a global variable that's matched in your report SQL. In 11g, the process is really the same, BI Publisher just provides a bit more help to define what trigger code needs to be called. You still need to create the function and place it inside a package in the db.
Here's a simple plsql package with the 'beforedata' function trigger.
create or replace PACKAGE BIREPORTS AS whereCols varchar2(2000); FUNCTION beforeReportTrig return boolean; end BIREPORTS;
create or replace PACKAGE BODY BIREPORTS AS FUNCTION beforeReportTrig return boolean AS BEGIN whereCols := ' and d.department_id = 100'; RETURN true; END beforeReportTrig; END BIREPORTS;
you'll notice the additional where clause (whereCols - declared as a public variable) is hard coded. I'll cover parameterizing that in my next post. If you can not wait, check the 10g docs for an example.
I have my package compiling successfully in the db. Now, onto the BIP data model definition.
1. Create a new data model and go ahead and create your query(s) as you would normally.
2. In the query dialog box, add in the variables you want replaced at runtime using an ampersand rather than a colon e.g. &whereCols.
select d.DEPARTMENT_NAME, ... from "OE"."EMPLOYEES" e, "OE"."DEPARTMENTS" d where d."DEPARTMENT_ID"= e."DEPARTMENT_ID" &whereCols
Note that 'whereCols' matches the global variable name in our package. When you click OK to clear the dialog, you'll be asked for a default value for the variable, just use ' and 1=1' That leading space is important to keep the SQL valid ie required whitespace. This value will be used for the where clause if case its not set by the function code.
3. Now click on the Event Triggers tree node and create a new trigger of the type Before Data. Type in the default package name, in my example, 'BIREPORTS'. Then hit the update button to get BIP to fetch the valid functions.
In my case I get to see the following:
Select the BEFOREREPORTTRIG function (or your name) and shuttle it across.
4. Save your data model and now test it. For now, you can update the where clause via the plsql package.
Next time ... parametrizing the dynamic clause.
The argument over why Businesses should abandon Excel in favor of more structured tools has been raging for as long as I have had more than a casual exposure to Oracle products. From the standpoint of an IT user Excel appears to be a simplistic, flat-file-based, error-prone tool that careless people use, despite its obvious flaws. Petabytes of duplicative Excel spreadsheets clog network drives across the globe; we as IT users know it, and it drives us crazy. Why, oh why, can’t these analysts, project managers, and accountants not grasp the elegant beauty of a centralized database solution that ensures data integrity, security, and has the chops to handle gobs of data, and abandon their silly Excel sheets?
I’ll tell you why: Excel is better. Excel the most flexible and feature-rich tool for organizing and analyzing data. Ever. Period.
For the past few years I have lived in a hybrid Finance/IT role, and in coming from IT, I was shocked at how much Excel was used, for everything. But after working with Excel on a daily basis for several years, I am a convert. An adept Excel user can out-develop any tool ( BI, Apex, Hyperion, Crystal Reports ) handily. (when dataset size is not an issue). Microsoft has done too much work on Excel, made it too extendable, too intuitive, built in so much, that no structured tool like BI, APEX, SAP, Hyperion will EVER catch up to its usability/flexibility.
Take this real-world example that came across my desk a few months ago: for a retail chain define a by-week, by-unit sales target, and create a report that compares actual sales to this target. Oh, and the weekly sales targets get adjusted each quarter based on current financial outlook.
How quickly could you turn around a DW/BI solution to this problem? What would it involve?
• Create table to house targets
• Create ETL process to load new targets
• Define BMM/Presentation Layers to expose targets
• Develop / test / publish report.
A day? Maybe? If one person handled all steps (unlikely, since the DB layers and RPD layers are probably handled by different people.)
I can tell you how long it took me in Excel: 3 hours (OBIEE driven data-dump, married with target sheet supplied to me). I love OBIEE, but Excel was still miles faster/more efficient for this task. And I could regurgitate 6 other examples like this one off at a moment’s notice.
Case in point: 95% of the data that C-level executives use to make strategic decisions is Excel based.
If you’ve ever sat in on a presentation to a CEO or other C-level executive at any medium to large sized company, you know that people are not bringing up dashboards, or any other applications. They are presenting PowerPoints with a few (less than 7) carefully massaged facts on them. If you trace the source of these numbers back down the rabbit hole, your first stop is always Excel. Within these Excel workbooks you will find “guesses” and “plugs” that fill gaps in solid data, to arrive at an actionable bit of information. It’s these “guesses” and “plugs” that are very hard to code for in an environment like OBIEE (or any other application). Can it be done? Yes, of course, with gobs of time and money. And during the fitful and tense development, the creditably of the application is going to take major hits.
Given the above, the usefulness of OBIEE might seem bleak. But I strongly feel that applications such as OBIEE do have a proper place in the upper organizational layers of modern business: Facilitating the Tactical business layers, and providing data-dumps to the Strategic Business layers.
Since this post is mainly about Excel, I will focus on how OBIEE can support the analyses that are inevitably going to be done in Excel.
Data Formatting, Data Formatting, FORMATTING!! I can’t stress this enough. For an analyst, having to re-format numbers that come out of an export so that you can properly display them or drive calcs off them in Excel is infuriating, and wasteful. My favorite examples: in a BI environment I worked with percentages were exported as TEXT, so while they looked fine in the application, as soon as you exported them to Excel and built calcs off them, your answer was overstated by a factor of 100 (Excel understood “75%” to be the number 75 with a text character appended, not the number 0.75).
Ask your users how they would like to SEE a fact in Excel: with decimals or not? With commas or not? Ensure that when exported to Excel, facts and attributes function correctly.
“Pull” refreshes of information sources in Excel. In the finance world, most Excel workbooks are low to medium complexity financial models, based off a data-dump from a reporting system. When the user wants to refresh the model, they refresh the data-dump, and the Excel calculations do the rest. OBIEE currently forces a user to “push” a new data-dump by manually running/exporting from OBIEE and then pasting the data into the data-dump tab in the workbook. What an Excel user really desires is to have a data dump that can be refreshed automatedly, using values that exist on other parts of the workbook to define filters of data-dump. Then all the user needs to do is trigger a “pull” and everything else is automated. Currently OBIEE has no solution to this problem that is elegant enough for the common Excel user. (Smartview must have its filters defined explicitly in the Smartview UI each time an analysis is pulled.)
The important part to take away from these 2 suggestions, and this entire post, is that to maximize the audience of OBIEE, we must acknowledge that Excel is the preferred tool of the Finance department, due to its flexibility, and support friendly exports to Excel as a best practice. We must also understand that accounting for this flexibility in OBIEE is daunting, and probably not the best use of the tool. If your users are asking for a highly complex attribute or fact, that is fraught with exceptions and estimations, chances are they are going to be much happier if what you give them is reliable information in a data-dump form, and allow them to handle the exceptions and estimations in Excel.
The latest release of Oracle’s data replication tool, GoldenGate 12c, has many improvements. The release coincided with the Oracle Data Integrator 12c release – and I wasn’t about to let my colleague Jérôme Françoisse have all of the fun describing new features! This post will walk through the GoldenGate 12c features that I think are relevant to data warehouse and data migration work we typically see in the business intelligence world.GUI Installer
First and foremost, the product has now graduated to using our friend the Oracle Universal Installer. There really isn’t much to the installation itself, but it can also perform a couple of tasks that we used to have to do manually; create subdirectories and create the manager parameter file.
After unzipping the installation file and launching runInstaller, we are asked to choose which version of Oracle database, 11g or 12c, GoldenGate will be extracting from or replicating to (depending on whether the install is occurring on the source or target server). Next, choose a file directory location for the Oracle GoldenGate home. The install process will always create the Manager parameter file in this directory. But, if you choose the “Start Manager” checkbox, it will also add the Manager Port to the parameter file, create the subdirectories, set the library paths (which is why we specify the database location), and start-up the manager process.
As you can see in the installation process, GoldenGate 12c now supports extracting from and applying to Oracle 12c Database. It also supports one of the great new features of Oracle 12c, multi-tenant container databases (CDB).
The GoldenGate extract can be set to pull from multiple pluggable databases (PDB) or containers in the source, combining the changed data into a single trail file. The replicat, however, will need to be split out into multiple process groups in order to apply the changes to a target pluggable database.Extract New Features
Beyond overall improved performance for integrated capture, here are a few other notable extract enhancements.Native DDL Capture
Now in integrated capture mode, when synchronizing source DDL changes, they are captured from the database logmining server. In OGG 11g, the DDL setup scripts installed triggers to capture the DDL changes. This intrusive action has now been eliminated.Set Extract to a Specific SCN
The ADD EXTRACT and ALTER EXTRACT commands now have a new SCN option. This will allow you to point the extract process at a specific SCN in the source transaction log. Also, the START EXTRACT command has the options ATCSN or AFTERCSN, which work the same as they do with the replicat file. This can help in positioning the extract process after a failure, to skip transactions, or during the initial load of a target database.Replicat New Features Integrated Replicat
The new integrated mode for the replicat allows the process to use logical-change records (LCR), passed to the database inbound server via a lightweight streaming, built-in API, to apply changes to the target database. This increases the performance of the replicat process. It also handles referential integrity and DDL operations, ensuring the correct order when changes are applied, handling parent-child relationships and removing that responsibility from the GoldenGate administrator. Turn on integrated mode by using the INTEGRATED option with the ADD REPLICAT command. Additional options can be added to the replicat parameter file using DBOPTIONS INTEGRATEDPARAMS <OPTION…>.
Integrated apply mode is very easy to configure and is a great way to increase replicat processing performance.Coordinated Mode
While the standard, classic replicat still exists, another option to increase performance for the apply process is to use coordinated mode. Similar to integrated mode, in coordinated mode the replicat applies changes in a multi-threaded, parallel fashion. The difference, though, is that coordinated mode continues to use the classic apply method – by constructing a SQL statement based on the trail file transaction to insert, update, or delete records in the target.
When adding the replicat, use the COORDINATED option to turn on coordinated mode. You can also control the maximum number of threads being used. The command SYNCHRONIZE REPLICAT can be used to synchronize all threads to the same position in the trail file, should there be a need during recovery of the replicat process after an unclean shutdown.Duplicate Transactions
Use the option FILTERDUPTRANSACTIONS when starting the replicat process to ignore already processed transactions. Very useful when the extract is repositioned.Other Notable Enhancements SQL-92 Rules
GoldenGate 12c now supports SQL-92 rules for quotations. This means that in the parameter files, and obey files, single quotes are used for literals and double quotes are used for object names. This is noted because the rule for single-quoted literals was not enforced in previous versions and will require a review of how quotes are used in the parameter files prior to upgrading.Security
GoldenGate encryption of both database username and passwords and trail files across TCP/IP has been enhanced, and made easier to implement. A credential store can be used to store the database user IDs and passwords, allowing an alias to be used in the parameter file rather than the old encryption key / encrypted password that was necessary in past versions. For trail file encryption, a wallet and master key approach is used to ensure the trail file data remains encrypted during transfer across the network. Each time a new trail file is created, a new encryption key is automatically generated.
Well, these are the features that stood out most when I reviewed the Oracle GoldenGate 12c Release Notes. Are there any new features that will make your life easier as a GoldenGate implementer/administrator that I may have missed? If so, let me know in the comments below.
Over the past week Venkat, myself and the Rittman Mead India team have been running a series of BI Masterclasses at locations in India, in conjunction with ODTUG, the Oracle Development Tools User Group. Starting off in Bangalore, then traveling to Hyderabad and Mumbai, we presented on topics ranging from OBIEE through Exalytics through to EPM Suite and BI Applications, and with networking events at the end of each day.
Around 50 attended at Bangalore, 30 in Hyderbad and 40 in Mumbai, at at the last event we were joined by Harsh Bhogle from the local Oracle office, who presented on Oracle’s high-level strategy around business analytics. Thanks to everyone who attended, thanks to ODTUG for sponsoring the networking events, and thanks especially to Vijay and Pavan from Rittman Mead India who organised everything behind the scenes. If you’re interested, here’s a Flickr set of photos from all three events (plus a few at the start where I visited our offices in Bangalore.)
For anyone who couldn’t attend the events, or if you were there and you’d like copies of the slides, the links below are for the PDF versions of the sessions we presented at various points over the week.
- Oracle BI, Analytics and EPM Product Update
- Extreme BI: Agile BI Development using OBIEE, ODI and Golden Gate
- OBIEE 11g Integration with the Oracle EPM Stack
- OBIEE and Essbase on Exalytics Development & Deployment Best Practices
- OBIEE 11g Security Auditing
- Intro and tech deep dive into BI Apps 11g + ODI
- Metadata & Data loads to EPM using Oracle Data Integrator
So I’m writing this in my hotel room in Mumbai on Sunday morning, waiting for the airport transfer and then flying back to the UK around lunchtime. It’s been a great week but my only regret was missing the UKOUG Apps’13 conference last week, where I was also supposed to be speaking but managed to double-book myself with the event in India.
In the end, Mike Vickers from Rittman Mead in the UK gamely took my place and presented my session, which was put together as a joint effort with Minesh Patel, another of the team in the UK and one of our BI Apps specialists. Entitled “Oracle BI Apps – Giving the Users the Reports they *Really* Want”, it’s a presentation around the common front-end customisations that we typically carry out for customers who want to move beyond the standard, generic dashboards and reports provided by the BI Apps, and again if you missed the session or you’d like to see the slides, they’re linked-to below:
That’s it for now – and I’ll definitely be at Tech’13 in a few weeks’ time, if only because I’ve just realised I’m delivering the BI Masterclass sessions on the Sunday, including a session on OBIEE/ODI and Hadoop integration - I’ve been saying to myself I’d like to get these two tools working with Impala as an alternative to Hive, so that gives me something to start looking at on the flight back later today.
In my previous post, I introduced the steps necessary for integrating Oracle BI Applications 18.104.22.168.1 and GoldenGate (OGG). Now, I’m going to dive into the details and describe how to complete the setup and configuration of GoldenGate and the Source Dependent Data Store schema. As I mentioned before, this process will closely follow Oracle’s documentation on “Administering Oracle GoldenGate and Source Dependent Schemas“, providing additional information and insight along the way.User and Schema Setup
The first step is to manually create the GoldenGate user on the source and target databases. These users, along with the Source Dependent Data Store schema, are not created by the BI Apps installer like the other standard schemas. This will be a dedicated user for OGG, and will have privileges specific to the needs of the extract process on the source and the replicat process on the target.Create Source GoldenGate User
Beginning with the source, create the user and grant the initial privileges. Be sure your tablespace has already been created.
-- Create OGG User on the source CREATE USER ogg_user IDENTIFIED BY Password01 DEFAULT TABLESPACE ggs_data QUOTA UNLIMITED ON ggs_data; GRANT CREATE SESSION TO ogg_user; GRANT ALTER SESSION TO ogg_user; GRANT SELECT ANY DICTIONARY TO ogg_user; GRANT FLASHBACK ANY TABLE TO ogg_user;
The specific table grants will not be made until later on via a script generated by an ODI Procedure, as the GoldenGate user does not need SELECT ANY TABLE privileges. On the other hand, the user does temporarily need ALTER ANY TABLE in order to set up supplemental logging for individual tables. Later on, this privilege can be revoked.
GRANT ALTER ANY TABLE TO ogg_user;
Finally, we’ll setup supplemental logging at the database level, ensuring the necessary information is logged for each transaction.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;Create Target GoldenGate User
Next, we’ll go out to the target server and create the GoldenGate user with target-specific privileges. Since GoldenGate performs the DML on the target, based on the change made in the source database, the user will need to be granted privileges to INSERT, UPDATE, DELETE. Again, rather than grant INSERT ANY TABLE, etc., the specific table grants will be generated as a script via an ODI Procedure.
-- Create OGG User CREATE USER ogg_target IDENTIFIED BY Password01 DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS; GRANT CREATE SESSION TO ogg_target; GRANT ALTER SESSION TO ogg_target; GRANT SELECT ANY DICTIONARY TO ogg_target;
We’ll be creating the checkpoint table via GoldenGate, so this user will temporarily need to be granted the CREATE TABLE privilege. The checkpoint table will keep track of the latest position in the target trail file, allowing a clean recovery should the target database go offline.
GRANT CREATE TABLE TO ogg_target;Create SDS User
Now we’ll create the SDS user and schema. A separate SDS schema must be created for each OLTP source application, as the SDS schema will essentially act as the source schema. We’ll follow the recommended naming conventions for the schema: <BIAPPS>SDS<Model Code>_<DSN>. BIAPPS is the user defined code signifying this is a BI Applications schema. To keep it simple, we’ll use BIAPPS. The Model Code is the unique code assigned to the data source and the DSN is the data source number for that source application.
In this example using Peoplesoft Campus Solutions, the SDS schema name is BIAPPS_SDS_PSFT_90_CS_20. Not a very friendly name to type, but serves its purpose in identifying the source of the schema data.
-- Create tablespace. CREATE TABLESPACE BIAPPS_SDS_PSFT_90_CS_20_TS DATAFILE '/u01/app/oracle/oradata/orcldata/BIAPPS_SDS_PSFT_90_CS_20..dbf' SIZE 100M AUTOEXTEND ON NEXT 10M LOGGING DEFAULT COMPRESS FOR OLTP; -- Create SDS User CREATE USER BIAPPS_SDS_PSFT_90_CS_20 IDENTIFIED BY Password01 DEFAULT TABLESPACE BIAPPS_SDS_PSFT_90_CS_20_TS QUOTA UNLIMITED ON BIAPPS_SDS_PSFT_90_CS_20_TS; -- Required Grants GRANT CREATE SESSION TO BIAPPS_SDS_PSFT_90_CS_20; GRANT CREATE TABLE TO BIAPPS_SDS_PSFT_90_CS_20;
Finally, the GoldenGate target user must be granted access to use the SDS tablespace for inserts/updates.
-- OGG user must be granted Quota to insert and update data ALTER USER ogg_target QUOTA UNLIMITED ON BIAPPS_SDS_PSFT_90_CS_20_TS;Install and Configure GoldenGate
The schemas are in place, so the next part of the setup is to install and configure the GoldenGate application on both the source and target servers. The GoldenGate installation process is pretty well documented on Gavin Soorma’s blog, so I won’t go into much detail here. The Oracle BI Applications documentation also has some example scripts, which take you through the setup of the extract, data pump, and replicat group processes.
The naming standards for the parameter files are fairly straightforward, with DSN being the same data source number we used in the SDS schema name.
- Extract: EXT_DSN
- Data Pump: DP_DSN
- Replicat: REP_DSN
Following the OBIA documentation examples, you will end up with each process group setup and the checkpoint table created in the target GoldenGate schema. I prefer to create an obey file for both the source and target setup scripts, similar to the following example.
--stop manager on target db dblogin userid ogg_target, password Password01 stop mgr --stop gg processes stop rep_20 delete rep_20 --delete CHECKPOINTTABLE DELETE CHECKPOINTTABLE ogg_target.OGGCKPT --delete previous trail files SHELL rm ./dirdat/* --start manager on target db start mgr --create CHECKPOINTTABLE in target db dblogin userid ogg_target, password Password01 ADD CHECKPOINTTABLE ogg_target.OGGCKPT add replicat rep_20, exttrail ./dirdat/tr, CHECKPOINTTABLE ogg_target.OGGCKPT
Using an obey script allows me to rerun the process should there be any sort of issue or failure and also provides me with a template that I can use for additional sources and SDS targets. The result should be process groups setup and ready to roll (once the parameter files are in place, of course).
Remember to revoke the CREATE TABLE privilege from the target GoldenGate user once the checkpoint table has been created.
REVOKE CREATE TABLE FROM ogg_target;
In the next post, I’ll walk through the SDS setup in OBIA and ODI, as well as the ODI Procedures that help generate the GoldenGate parameter files, SDS schema DDL, and initial load scripts.
Oracle BI Apps 22.214.171.124.1 – GoldenGate Integration
That’s it! The long awaited Oracle Data Integrator 12c is out! You can find the 12.1.2 version on the ODI Downloads page and discover the new features here while it gets downloaded. The main news is surely the new flow-based paradigm and the ability to load multiple targets within the same interface… Oh no wait, we don’t talk about “interfaces” anymore, it’s now called “mappings”! Fantastic, ODI developers can now communicate with developers using other ETL tools using the same vocabulary. This new terminology and the flow-based paradigm also bring ODI and Oracle Warehouse Builder (OWB) closer than ever. Let’s have a glance at some of the new features.
So this release brings a whole new way to develop your integration jobs. In the new logical tab of your mapping, you can drag and drop source datastores from your model as in the previous versions of ODI. But you will also have to drag your target datastores in the same canvas.
To map your source to your target and build the logic, a new “component” panel, very similar to the OWB one, has been added on the right hand side of ODI Studio. From there you can drag and drop join, filter, expression, union and lookup components into your canvas. There is even a distinct component, which means we are done with creating a yellow interface just to select distinct rows from the source. Isn’t it nice? And more than that, it was announced during OOW that more components stand on the ODI roadmap.
Every datastores and components have an IN and an OUT connector. Dataflows are created by dragging a connector on another one. What is interesting is that you can map an OUT connector to multiple IN connectors, and therefore load multiple targets at the same time!
<old-school>Good news for the nostalgics, it’s still possible to develop a mapping using the interface-style paradigm… More information in a future post.</old-school>
The physical tab of you mapping is similar to the former flow tab of ODI 11g. That’s where you can select your KMs and their options. What is interesting is that you can now have multiple physical implementations – called deployment specifications – while keeping the same business logic. You can for instance create only one mapping for both your initial and your incremental load by selecting a different IKM in each of these deployment specifications (DS) and select which one you want to use at run-time.
The new mappings also introduce a few useful features :
- in-mapping lineage and impact analysis : When a column in one datastore/component of the mapping is selected, all the columns used to load it and all the columns loaded by it are highlighted.
- syntax highlighting appears in the expression fields.
- autocompletion is available in every expression fields. Columns are suggested based on the few characters already typed. This is the best announcement ever for a lazy developer like me!
ODI 12c introduces the concept of reusable mapping, similar to those in OWB. It is designed like a standard mapping except that it use an input and/or an output signature in replacement of datastores. This allow to reuse it in multiple regular mappings by connecting these signatures to other components.
When upgrading from ODI 11g to ODI 12c, yellow (temporary) interfaces will turned on Reusable Mappings.
Instead of running a mapping, you can now also debug it from ODI Studio. A brand new pane appears where you can see the blueprints of your mapping. From there, you can set breakpoints or you can control your execution step by step. Thanks to this, it’s possible review to temporary state of your data or the values of variables at every step of an execution.
What about your old OWB jobs?
ODI 12.1.2 enables the execution and monitoring of OWB jobs within ODI. A new OWB technology is now supported in the topology in order to plug the OWB repository. Once it’s done you can run an OWB mapping or process flow from a package or a procedure thanks to a new ODI Tool called OdiStartOwbJob.
According to the ODI and OWB Statement of Direction, a migration utility should be available later to automatically translate some of the OWB objects in their ODI equivalent. Of course some manual work will be required as well.
To be continued…
Of course Part 2 is coming with other new features… Also expect a lot more to come from other Rittman Mead guys in the next few days!
[Update 04-Nov-2013] The second part is published.
There will come a point in the lifecycle of an OBIEE deployment when one user will need to access another user’s account. This may be to cover whilst a colleague is on leave, or a support staff trying to reproduce a reported error.
Password sharing aside (it’s zero-config! but a really really bad idea), OBIEE supports two methods for one user to access the system as if they were another: Impersonation and Act As.
This blog article is not an explanation of how to set these up (there are plenty of blogs and rip-off blogs detailing this already), but to explain the difference between the two options.
First, a quick look at what they actually are.Impersonation
Impersonation is where a “superuser” (one with
oracle.bi.server.impersonateUser application policy grant) can login to OBIEE as another user, without needing their password. It is achieved in the front end by constructing a URL, specifying:
- The superuser’s login name and password (
- The login ID of the user to impersonate (
The server will return a blank page to this request, but you can then submit another URL to OBIEE (eg the OBIEE catalog page or home page) and will already be authenticated as the Impersonate user – without having specified their password.
From here you can view the system as they would, and carry out whatever support or troubleshooting tasks are required.
Caution : Impersonation is disabled by default, even for the weblogic Administrator user, and it is a good idea to leave it that way. If you do decide to enable it, make sure that the user to whom you grant it has a secure password that is not shared or known by anyone other than the account owner. Also, you will see from the illustration above that the password is submitted in plain text which is not good from a security point of view. It could be “sniffed” along the way, or more easily, extracted from the browser history.Act As
Whilst Act As is a very similar concept to Impersonation (allow one user to access OBIEE as if they were another), Act As is much more controlled in how it grants the rights. Act As requires you to specify a list of users who may use the functionality (“Proxy users”), and for each of the proxy users, a list of users (“Target users”) who they may access OBIEE as.
Act As functionality is accessed from the user dropdown menu :
From where a list of users that the logged-in user (“proxy user”) has been configured to be able to access is shown :
Selecting a user switches straight to it:
In addition to this fine grained specification of user:user relationships, you can specify the level of access a Proxy user gets – full, or read-only. Target users (those others can Act As) can see from their account page who exactly has access to their account, and what level of access.
So what’s the difference?
Here’s a comparison I’ve drawn up
Here are a couple of examples to illustrate the point:
Based on this, my guidelines for use would be :
- As an OBIEE sysadmin, you may want to use Impersonate to be able to test and troubleshoot issues. However, it is functionality much more intended for systems integration than front-end user consumption. It doesn’t offer anything that Act As doesn’t, except fewer configuration steps. It is less secure that Act As, and could even be seen as a “backdoor” option. Particularly at companies where audit/traceability is important should be left disabled.
- Act As is generally the better choice in all scenarios of an OBIEE user needing the ability to access another’s account, whether between colleagues, L1/L2 support staff, or administrators.
Compared to Impersonation, it is more secure, more flexible, and more granular in whose accounts can be accessed by whom. It is also fully integrated into the user interface as standard functionality of the tool.
- Act As: Enabling Users to Act for Others
- Impersonate: How to use OBIEE Impersonate parameter for quick checks
Thanks to Christian Berg, Gianni Ceresa and Gianni Ceresa for reading drafts of this article and providing valuable feedback
The release of Oracle Business Intelligence Applications 126.96.36.199.1 includes a major change in components, with Oracle Data Integrator replacing Informatica as the ETL application. The next logical step was to integrate Oracle’s data replication tool, Oracle GoldenGate, for a real-time load of source system data to the data warehouse. Using GoldenGate replication rather than a conventional extract process, contention on the source is essentially eliminated and all of the source OLTP data is stored locally on the data warehouse, eliminating network bottlenecks and allowing ETL performance to increase. In this series of posts, I’m going to walk through the architecture and setup for using GoldenGate with OBIA 188.8.131.52.1.GoldenGate and the Source Dependent Data Store
For those of you not familiar with Oracle GoldenGate (OGG), it is the standard Oracle product for data replication, providing log-based change data capture, distribution, and delivery in real-time.
GoldenGate captures transactional data changes from the source database redo log and loads the changes into its own log file, called a Trail File, using a platform-independent universal data format. The Extract process understands the schemas and tables from which to capture changes based on the configuration set in the Extract parameter file. The data is then read from the Source Trail File and moved across the network to the Target Trail File using a process called a Data Pump, also driven by a parameter file. Finally, the transactions are loaded into the target database tables using the Replicat parameter file configuration, which maps source tables and columns to their target. The entire process occurs with sub-second latency and minimal impact to the source and target systems.
In my previous blog posts regarding Oracle GoldenGate, I described how to replicate changes from the source to the Staging and Foundation layers of the Oracle Reference Architecture for Information Management. In OBIA, GoldenGate is used for pure replication from the source database to the target data warehouse, into what is known as the Source Dependent Data Store (SDS) schema.
The SDS is setup to look exactly like the source schema, allowing the Oracle Data Integrator pre-built Interfaces to change which source they are using from within the Knowledge Module by evaluating a variable (IS_SDS_DEPLOYED) at various points throughout the KM (we’ll look at this in more detail later on). Using this approach, the GoldenGate integration can be easily enabled at any point, even after initial configuration. In fact, that is exactly what I did – making the switch to using OGG after my first full data load from the source without GoldenGate. The Oracle BI Apps team did a great job of utilizing the features of ODI that allow the logical layer to be abstracted from the physical layer and data source connection.Getting Started – High Level Steps
To begin, we must first install Oracle BI Applications 184.108.40.206.1, if it is not already up and running in your environment. I followed the recently published OTN article, “Cookbook: Installing and Configuring Oracle BI Applications 220.127.116.11.1″, written by Mark Rittman and Kevin McGinley. Rather than use Windows, though, I decided to go with Linux as my host operating system for OBIA. This had its own challenges, but nothing’s worth doing if there isn’t a bit of learning involved! After generating the “Source Extract and Load” Load Plan, it’s time to setup GoldenGate.
Before we dig into the details of the GoldenGate integration, let’s review the necessary steps at a high-level. The process follows Oracle’s documentation on administering GoldenGate and OBIA Source Dependent Schema.
1. Configure the source and target database schemas.
We need to create a GoldenGate user on both the source and target databases, as well as the Source Dependent Data Store schema on the target, along with the appropriate grants, etc.
2. Install Oracle GoldenGate on the source and target servers.
Download and install GoldenGate on each server. The Oracle BI Applications documentation shows an example on how to get the configuration started.
3. Configure the Source Dependent Data Store.
Enable the SDS in the OBIA Configuration Manager and create a new Physical Schema for the SDS in Oracle Data Integrator.
4. Generate and execute the DDL to create tables in the SDS schema on the target database.
As part of the OBIA installation, many “standard” ODI Packages and Procedures were created, including GENERATE_SDS_DDL. By entering the appropriate values into the Options during execution, the process will generate a SQL script that can then be executed against the SDS.
5. Generate the initial load script.
Yet another OBIA delivered Procedure will generate a SQL script for the initial load from the source to SDS schema. The script will contain INSERT statements using a database link from target to source. This script may be useful if an outage were called on the source application during OBIA and GoldenGate integration setup. But, if transactions are still flowing into the source application, a different approach will need to be used. We’ll get into more details on this later (hint: it involves the source SCN).
6. Generate and deploy the GoldenGate parameter files.
This is where we might expect to see the “JKM Oracle to Oracle Consistent (OGG)” Journalizing Knowledge Module put to use, correct? But no, the OBIA product team decided to go with a custom Procedure rather than the JKM, as the ODI Change Data Capture (aka Journalizing) is not put to use. Execute the GENERATE_SDS_OGG_PARAM_FILES Scenario, copy the parameter files to the appropriate locations, and complete the GoldenGate configuration.
7. Start GoldenGate replication.
Again, if there is not a source outage we will probably need to customize our start replicat statement to ensure we do not miss any transactions from the source.
Once GoldenGate replication has begun and you can continue to use the same “Source Extract and Load” Load Plan that was originally generated to pull data from the source database into the data warehouse. But now, instead of reaching out to the source database, this Load Plan will pull data from the SDS schema into the staging area.
In the next post, we’ll dive deeper into the setup and configuration details, working through each of the steps listed above.
Oracle BI Apps 18.104.22.168.1 – GoldenGate Integration
Back in August a new Oracle mobile solution jumped out of the gate, the Mobile App Designer (MAD). I seem to have been on the road every week for the last, goodness knows how many weeks. I have finally found some time this week to get down and work with it. Its pretty cool and above all, its capable of providing a mobile platform independent reporting solution.
But you already have a mobile application! Yep, and I think they both sit quite comfortably together. The Oracle BI Mobile Application is available from the App Store for Apple users. Its a great app, build reports, dashboards and BIP reports for your browser based users and your Apple app users can take advantage of them immediately.
MAD takes the next step forward. Maybe you don't use or can not use Apple mobile devices? Maybe you need to build something more specific around a business area that provides users with a richer experience, beyond what Answers and Dashboards can offer. However, you do not want to have to rely of the tech folks to build the mobile application, thats just piling more work on them. You also want to be platform agnostic, you might have a mix of mobile platforms. MAD can help.
For those of you that have already used the Online Template layout editor with BI Publisher, you already know how to build a mobile application. The MAD interface is essentially the online template builder user interface, tweaked for a mobile destination ie a phone or tablet.
You can build and test in a browser and then deploy to your own BI App Store. Users, on their mobile devices, can then subscribe to an application. They can open and interact with your app using their phone or tablet's interactive features just as they would with a native application. As you update your app and add new features the changes will be picked up the next time your users open the application.
Interested? Want to know more? The Oracle MAD home page has a ton of content including tutorials, etc. We are planning to dig into MAD in forthcoming posts. The geek in me wanted to be able to build plugins using the D3 and other visuals. I have been working with Leslie on some of the documentation and we'll be sharing some of that 'plugin' doc and how tos in the coming weeks.
The UK OUG Apps 2013 conference is a must attend for users of Oracle Applications. Held at the Brewery in London, there are three full days of content with 10 streams and over 150 speakers.
Rittman Mead are proud to be the Analytics Sponsor for the event and have two speaker sessions at the event.
Click on the details below to view the session abstracts and more details
Speaker: James Knight – Head of Advanced Analytics
Date: Monday 14th October at 16.40 in the Queen Charlotte Hall
Speaker: Mike Vickers
Date: Tuesday 15th October at 10.20 in the King Vault Hall
We look forward to seeing you there
The Rittman Mead Team
One of the data visualisations that you can use with Oracle Endeca Information Discovery is the “tag cloud”. You’ve probably seen tag clouds used in newspaper articles and other publications to show the most commonly found words or phrases in a document, the screenshot below shows a tag cloud in Endeca built on data sourced from comments in a social media feed.
The component within Endeca Information Discovery that extracted the bank names from the data feed is called the “text enrichment engine”, which actually uses technology that Oracle license from a company called Lexalytics. When you use the text enrichment engine, one of the things it does is to extract “entities” such as people, companies, products, places, email addresses and dates, along with themes and any quotations mentioned in the text.
However, as you might have noticed from the tag cloud, several of the banks and other institutions that this process extracts have a few different variations in their name – for example, Amex is also shown as AmEx, AMEX and so on – but obviously these all actually refer to the same company, American Express. So how can we display tag clouds in Endeca but deal with this data issue in the background?
Another issue that can occur is that some words may be ignored or mistakenly allocated to the wrong group of entities. For example, I had “OMG” picked up as a company name, which is correct, but by checking the data itself it proved to be a shortening of “Oh My God” in the text!!
One solution to these kinds of problems is to use the Text-Tagger component within Endeca Information Discovery, the data-loading tool that is used to load Endeca Server data domains. Using this Text-Tagger component, you can prepare a list of in this example, companies of interest in advance, and the component will find and tag any record with the pre-defined tag, including ignoring case if required.
In some circumstances you will want to create a new list based on the application that you are working on. An example related to the displayed image is a list of all bank or financial institutes and their acronyms. It could be the case that we want to exclude company names that are not related to banking, rather than as shown in the image above where Amazon appears for example.
To solve any of above cases, the text enrichment engine supports customer-defined lists (CDL). In the example below, I’ll create one of these lists and use it to clean up the organisation naming so that my tag cloud shows the correct names for each organisation.
1. First Create a Customer-Defined list and save it as custom.cdl postfix in ..\Lexalytics\data\user\salience\entities\lists. As a rule, the file format must be similar to this:
Lloyds TSB<tab> Lloyds
British Broadcasting Corporation<tab>BBC
2. Update the Text Enrichment data directory. The default directory is normally Lexalytics\data but after applying the cdl file it should point to Lexalytics\data\user instead.
3. Update the Salience.properties file within Oracle Endeca Information Discovery ETL tool, Integrator designer. By default the property “te.entity.types” contains Company, Person, Place, Product, Sports and Title. Add “List” to include user-defined entities.
5. Running the new graph and configurations, here is the new tag cloud using the new entity I created: