Tag Archives: Uncategorized
RM BI Forum 2015 : Justification Letters for Employers
(Thanks to Christian Berg @Nephentur for the suggestion, and acknowledgements to ODTUG KScope for the original idea – our favourite conference after the BI Forum)
The Rittman Mead BI Forum 2015 promises to be our best BI Forum yet, with fantastic speakers at each event, keynotes and guest speakers from Oracle and John Foreman, author of the bestselling book “Data Smart”, a data visualisation challenge and an optional one-day masterclass on delivering Oracle’s new Information Management and Big Data reference architecture by Rittman Mead’s Mark Rittman and Jordan Meyer. Uniquely amongst Oracle BI events we keep the numbers attending very limited and run just a single stream at each event, so everyone takes part in the same sessions and gets to meet all the attendees and speakers over the three days.
Sometimes though, management within organizations require special justification for team members to attend events like these, and to help you put your case together and get across the unique education and networking benefits of the Rittman Mead BI Forum, we’ve prepared justification letters for you to complete with your details, one each for the Brighton and Atlanta events. Click on the links below to download sample justification letters for the Brighton BI Forum running on May 6th-8th 2015, and the Atlanta one running the week after on May 13th-15th 2015:
- BI Forum 2015 Brighton Approval Letter, May 6th-8th 2015 (MS Word, 131KB)
- BI Forum 2015 Atlanta Approval Letter, May 13th-15th 2015 (MS Word, 137KB)
Full details on the BI Forum 2015 agenda and how to register can be found on the Rittman Mead BI Forum 2015 home page, with registration open until the weekend before each event – hurry though as attendee numbers are strictly limited.
Rittman Mead BI Forum Abstract Voting Now Open – For One Week Only!
The call for papers for the Rittman Mead BI Forum 2015 closed a couple of weeks ago and we’ve had some excellent submissions on topics ranging from OBIEE, Visualizations and data discovery through to in-memory analytics, cloud, big data and data integration. As always, we’re now opening up the abstract submission list for scoring, so that anyone considering coming to either the Brighton or Atlanta events can have a say in what abstracts are selected.
The voting forms, and event details, are below:
- Brighton, UK : Hotel Seattle, Brighton, UK : May 6th – 8th 2015 : Abstract Voting Form
- Atlanta, GA : Renaissance Atlanta Midtown Hotel, Atlanta, USA : May 13th-15th 2015 : Abstract Voting Form
Voting is open for just one week, and will close at 5pm this coming Sundy (Feb 8th 2015) . Shortly afterwards we’ll announce the speaker line-up, and open-up registrations for both events. For info, we’ve got a couple of additional abstracts coming in from Oracle on OBIEE12c, BI Cloud and Big Data Discovery, which I’ll be considering as part of the final line-up for the two events.
Keep an eye on the blog later in February for the final speaker line-up, and details of how to register for Brighton and Atlanta.
Why and How to use Oracle Metadata Management 12c. Part 1: Getting Started
At OOW 2014, Oracle announced the new Oracle Metadata Management solution and later in the middle of October released its first version – OMM 12.1.3.0.0
At the end of November of 2014, the second version was released -OMM 12.1.3.1.0- with new features and some bugs fixed.
But first things first, what is Oracle Metadata Management? And why we want to use it?
One of the biggest problems that we face today, is the proliferation of different systems, data sources, solutions for BI, for ETL, etc in the same company. So not only for final users but also for technical people (from SysAdmin, Data Scientist, Data Steward to Developers) is quite difficult to track which data is used by which applications. In some cases is almost impossible to perform an impact analysis if someone wants to change a table or if the way that a sales measure is calculated needs to change. With more systems involved, the problem is bigger.
Oracle Metadata Management (OMM) comes to provide a solution to this problem. It is a complete metadata management platform that can reverse engineer (harvest) and catalog metadata from any source: relational, Big data, ETL, BI, data modelling, etc.
OMM allows us to perform interactive searching, data lineage, impact analysis, semantic definition and semantic usage analysis within the catalog. And the really important thing is the metadata from different providers (Oracle or/and third-party) can be related (stitched) so you will have the complete path of data from source to report or vice versa. In addition, it manages versioning and comparison of metadata models.
The Oracle Metadata Management solution offers two products: OEMM (Oracle Enterprise Metadata Management) and OMM for OBI (Oracle Metadata Management for Oracle Business Intelligence). With the first one we can use metadata providers from Oracle and third-party technologies. Using OMM for OBI allows us to use metadata for databases, OBIEE, ODI and DAC.
We will see in this series of posts how to use each of these options, the difference between them and which will be the best option depending of your environment.
In this first post we will focus on the installation process and the requirements for it.
Minimum Requirements for a small test environment
It is important to note and it is also well explained in the Readme document, that the following are the minimum requirements for a tutorial or a small business case, not for a larger system.
Browser
Any of these browsers or newer versions of them with at least Adobe Flash v8 plugging can be used: Microsoft Internet Explorer (IE) v10, Mozilla Firefox v30 or newer, Google Chrome v30, Apple Safari v6.
Hardware
2 GHZ or higher quad core processor
4 GB RAM (8 GB if 64bit OS using 64bits Web Application Server)
10 GB of disk space (all storage is primarily in the database server)
Operating System
Microsoft Windows 2008 Server, Windows 2012 Server, Windows 7, Windows 8, or Windows 8.1. Be sure that the you have full Administrator privilege when run the installer and that the Microsoft .NET Framework 3.5 or higher is installed.
Other operating systems require manual install/setup, so are not supported by this version.
Web Application Server
The installer comes with the Apache Tomcat as Web Application Server and Oracle JRE 6 as Java Run Environment. Others web application servers (including Oracle WebLogic) require manual install/setup, and are not supported by this version.
Database Server
For the Database Server you can only use an Oracle Database from 10gR2 to 12 64-bit as a repository for OMM. You can create a new instance or reuse your existing Oracle database server but we need to have admin privileges in the database.
A very important observation is that the character set MUST be AL32UTF8 (UTF8). This is because the Oracle Intermedia Search can only index columns of type VARCHAR or CLOB (not the national variants NVARCHAR and NCLOB respectively). Otherwise you will receive this error message when you run the OMM for the first time:
To solve this, you can create a new instance of the database, or if your database has data already, there a couple of notes in My Oracle Support 260192.1 and 788156.1 to change any character set to AL32UTF8.
In addition, the CTXSYS user must be exist in the database. In case it doesn’t exist, the creation and granting privileges script can be found in <ORACLE_HOME>/ctx/admin/catctx.sql.
Preparing to install
Step 1 - Download the software. You can download the software from the OTN site http://www.oracle.com/technetwork/middleware/oemm/downloads/index.html or using e-delivery.oracle.com instead.
Step 2 – Create a Database Schema as Repository. Before start the installation, a database schema needs to be created as a repository for OMM to keep all its objects like models, configurations, etc (we will see all of these objects in next posts)
For that reason create a user in the database:
“create user MIR identified by <password> quota unlimited on users”
And give to it the following grants:
“grant create session to MIR;
grant create procedure to MIR;
grant create sequence to MIR;
grant create table to MIR;
grant create trigger to MIR;
grant create type to MIR;
grant create view to MIR”
We also need to give grants to the new to user to execute a package from CTXSYS and another one from SYS.
“grant execute on CTXSYS.CTX_DDL to MIR;
grant execute on SYS.DBMS_LOCK TO MIR;”
If you prefer (and also could be a more accurate solution) you can create specific tablespaces (user tablespace and temp tablespace) for that user. I asked to David Allan, who is always very generous with his time and knowledge, if this schema will be part of the RCU in future releases but there is no plan to incorporate the MIR schema to it.
Installation and Post-Install tasks
Step 3 – Install the software. We can start now to run the installation. The downloaded zip file contains an exe file, double-click on it to start the installation.
In the first screen, select the type of product that you want to install: OEMM or OMM for OBI. We choose the Oracle Enterprise Metadata Management and press Next.
In the next screen, you have access to the Readme document and release notes pressing the View Readme button. After the installation you can find them in the OMM_Home/Documentation folder.
The next screen show you the destination location that you can change if you want. Keep the ports number suggested on the next screen.
The last screen of the installation ask you to restart the computer in order to use the product.
Step 4 – Start OMM Server as a service. After you restart the computer, you need to configure the OMM Server as a Service and start it. You can do this through the option that is showed in the start menu and press the Start button or going directly to the windows services screen and press the right button on the OMM service and start it.
Step 5 – Initialize OEMM. Run the OEMM for the first time. We have everything ready to start using Oracle Metadata Management. Go to the URL: http://localhost:11580/MM or execute the shortcut that was created on your desktop after the installation or use the Windows Start Menu.
We need to enter the connection details using the schema that we created in the database. Enter MIR as the Database User Id, its password and the database URL, and then press the Test Connection button. After you receive the Successful message, press the Save button to run the initialization process where OEMM create the objects in the database schema to manage the repository.
This process takes some minutes until you get the confirmation that the initialization process is also successful.
Step 6 – Start OEMM. Close the browser tab and open again the OEMM URL (http://localhost:11580/MM). A login page appears. User and password to login is Administrator/Administrator
This is the main page of the OEMM where we are going to harvest (reverse-engineer) the metadata from different providers in the next posts.
In case you want to change the password of the Administrator user go to Tools > Administration on the top right of the page. Select the Administrator user and the user will be appear below.
If you prefer to create another user with Administration privileges, just press the Add User button (plus icon) in the Administration page and enter the details for the new user:
We are using the Native LDAP authentication approach for this demo, but OEMM can also use an External LDAP for authentication.
About the product documentation you can access it through the Help option which is on the top right of the page. In the Contents tab you have all the topics (Harvesting, Administration, etc) separated by folder and in each of them all the details about the specific topic
Installation of OMM for OBI
There are no differences in the installation process for OEMM and OMM for OBI. Just be sure to select the one that you want in the first screen of the installation. This is the page to login to the OMM for OBI.
In the next post, we will see how is the harvest (importing metadata) process using different metadata providers like OBIEE, ODI and others.
Rittman Mead BI Forum 2015 Call for Papers Now Open – Closes on Jan 18th 2015
The Call for Papers for the Rittman Mead BI Forum 2015 is currently open, with abstract submissions open to January 18th 2015. As in previous years the BI Forum will run over consecutive weeks in Brighton, UK and Atlanta, GA, with the provisional dates and venues as below:
- Brighton, UK : Hotel Seattle, Brighton, UK : May 6th – 8th 2015
- Atlanta, GA : Renaissance Atlanta Midtown Hotel, Atlanta, USA : May 13th-15th 2015
Now on it’s seventh year, the Rittman Mead BI Forum is the only conference dedicated entirely to Oracle Business Intelligence, Oracle Business Analytics and the technologies and processes that support it – data warehousing, data analysis, data visualisation, big data and OLAP analysis. We’re looking for session around tips & techniques, project case-studies and success stories, and sessions where you’ve taken Oracle’s BI products and used them in new and innovative ways. Each year we select around eight-to-ten speakers for each event along with keynote speakers and a masterclass session, with speaker choices driven by attendee votes at the end of January, and editorial input from myself, Jon Mead and Charles Elliott and Jordan Meyer.
Last year we had a big focus on cloud, and a masterclass and several sessions on bringing Hadoop and big data to the world of OBIEE. This year we’re interested in project stories and experiences around cloud and Hadoop, and we’re keen to hear about any Oracle BI Apps 11g implementations or migrations from the earlier 7.9.x releases. Getting back to basics we’re always interested in sessions around OBIEE, Essbase and data warehouse data modelling, and we’d particularly like to encourage session abstracts on data visualization, BI project methodologies and the incorporation of unstructured, semi-structured and external (public) data sources into your BI dashboards. For an idea of the types of presentations that have been selected in the past, check out the BI Forum 2014, 2013 and 2012 homepages, or feel free to get in touch via email at mark.rittman@rittmanmead.com.
The Call for Papers entry form is here, and we’re looking for speakers for Brighton, Atlanta, or both venues if you can speak at both. All session this year will be 45 minutes long, all we’ll be publishing submissions and inviting potential attendees to vote on their favourite sessions towards the end of January. Other than that – have a think about abstract ideas now, and make sure you get them in by January 18th 2015 – just over a week from now!.
TIMESTAMPS and Presentation Variables
TIMESTAMPS and Presentation Variables can be some of the most useful tools a report creator can use to invent robust, repeatable reports while maximizing user flexibility. I intend to transform you into an expert with these functions and by the end of this page you will certainly be able to impress your peers and managers, you may even impress Angus MacGyver. In this example we will create a report that displays a year over year analysis for any rolling number of periods, by week or month, from any date in time, all determined by the user. This entire document will only use values from a date and revenue field.
The TIMESTAMP is an invaluable function that allows a user to define report limits based on a moving target. If the goal of your report is to display Month-to-Date, Year-to-Date, rolling month or truly any non-static period in time, the TIMESTAMP function will allow you to get there. Often users want to know what a report looked like at some previous point in time, to provide that level of flexibility TIMESTAMPS can be used in conjunction with Presentation Variables.
To create robust TIMESTAMP functions you will first need to understand how the TIMESTAMP works. Take the following example:
Here we are saying we want to include all dates greater than or equal to 7 days ago, or from the current date.
- The first argument, SQL_TSI_DAY, defines the TimeStamp Interval (TSI). This means that we will be working with days.
- The second argument determines how many of that interval we will be moving, in this case -7 days.
- The third argument defines the starting point in time, in this example, the current date.
So in the end we have created a functional filter making Date >= 1 week ago, using a TIMESTAMP that subtracts 7 days from today.
Note: it is always a good practice to include a second filter giving an upper limit like “Time”.”Date” < CURRENT_DATE. Depending on the data that you are working with you might bring in items you don’t want or put unnecessary strain on the system.
We will now start to build this basic filter into something much more robust and flexible.
To start, when we subtracted 7 days in the filter above, let’s imagine that the goal of the filter was to always include dates >= the first of the month. In this scenario, we can use the DAYOFMONTH() function. This function will return the calendar day of any date. This is useful because we can subtract this amount to give us the first of the month from any date by simply subtracting it from that date and adding 1.
Our new filter would look like this:
For example if today is December 18th, DAYOFMONTH(CURRENT_DATE) would equal 18. Thus, we would subtract 18 days from CURRENT_DATE, which is December 18th, and add 1, giving us December 1st.
(For a list of other similar functions like DAYOFYEAR, WEEKOFYEAR etc. click here.)
To make this even better, instead of using CURRENT_DATE you could use a prompted value with the use of a Presentation Variable (for more on Presentation Variables, click here). If we call this presentation variable pDate, for prompted date, our filter now looks like this:
A best practice is to use default values with your presentation variables so you can run the queries you are working on from within your analysis. To add a default value all you do is add the value within braces at the end of your variable. We will use CURRENT_DATE as our default, @{pDate}{CURRENT_DATE}. Will will refer to this filter later as Filter 1.
{Filter 1}:
As you can see, the filter is starting to take shape. Now lets say we are going to always be looking at a date range of the most recent completed 6 months. All we would need to do is create a nested TIMESTAMP function. To do this, we will “wrap” our current TIMESTAMP with another that will subtract 6 months. It will look like this:
Now we have a filter that is greater than or equal to the first day of the month of any given date (default of today) 6 months ago.
To take this one step further, you can even allow the users to determine the amount of months to include in this analysis by making the value of 6 a presentation variable, we will call it “n” with a default of 6, @{n}{6}. We will refer to the following filter as Filter 2:
{Filter 2}:
For more on how to create a prompt with a range of values by altering a current column, like we want to do to allow users to select a value for n, click here.
Our TIMESTAMP function is now fairly robust and will give us any date greater than or equal to the first day of the month from n months ago from any given date. Now we will see what we just created in action by creating date ranges to allow for a Year over Year analysis for any number of months.
Consider the following filter set:
This appears to be pretty intimidating but if we break it into parts we can start to understand its purpose.
Notice we are using the exact same filters from before (Filter 1 and Filter 2). What we have done here is filtered on two time periods, separated by the OR statement.
The first date range defines the period as being the most recent complete n months from any given prompted date value, using a presentation variable with a default of today, which we created above.
The second time period, after the OR statement, is the exact same as the first only it has been wrapped in another TIMESTAMP function subtracting 1 year, giving you the exact same time frame for the year prior.
This allows us to create a report that can run a year over year analysis for a rolling n month time frame determined by the user.
A note on nested TIMESTAMPS:
You will always want to create nested TIMESTAMPS with the smallest interval first. Due to syntax, this will always be the furthest to the right. Then you will wrap intervals as necessary. In this case our smallest increment is day, wrapped by month, wrapped by year.
Now we will start with some more advanced tricks:
- Instead of using CURRENT_DATE as your default value, use yesterday since most data are only as current as yesterday. If you use real time or near real time reporting, using CURRENT_DATE may be how you want to proceed. Using yesterday will be valuable especially when pulling reports on the first day of the month or year, you generally want the entire previous time period rather than the empty beginning of a new one. So, to implement, wherever you have @{pDate}{CURRENT_DATE} replace it with @{pDate}{TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE)}
- Presentation Variables can also be used to determine if you want to display year over year values by month or by week by inserting a variable into your SQL_TSI_MONTH and DAYOFMONTH statements. Changing MONTH to a presentation variable, SQL_TSI_@{INT}{MONTH} and DAYOF@{INT}{MONTH}, where INT is the name of our variable. This will require you to create a dummy variable in your prompt to allow users to select either MONTH or WEEK. You can try something like this: CASE MOD(DAY(“Time”.”Date”),2) WHEN 0 ‘WEEK’ WHEN 1 THEN ‘MONTH’ END
In order for our interaction between Month and Week to run smoothly we have to make one more consideration. If we are to take the date December 1st, 2014 and subtract one year we get December 1st, 2013, however, if we take the first day of this week, Sunday December 14, 2014 and subtract one year we get Saturday December 14, 2014. In our analysis this will cause an extra partial week to show up for prior years. To get around this we will add a case statement determining if ‘@{INT}{MONTH}’ = ‘Week’ THEN subtract 52 weeks from the first of the week ELSE subtract 1 year from the first of the month.
Our final filter set will look like this:
With the use of these filters and some creative dashboarding you can end up with a report that easily allows you to view a year over year analysis from any date in time for any number of periods either by month or by week.
That really got out of hand in a hurry! Surely, this will impress someone at your work, or even Angus MacGyver, if for nothing less than he or she won’t understand it, but hopefully, now you do!
Also, a colleague of mine Spencer McGhin just wrote a similar article on year over year analyses using a different approach. Feel free to review and consider your options.
Calendar Date/Time Functions
These are functions you can use within OBIEE and within TIMESTAMPS to extract the information you need.
- Current_Date
- Current_Time
- Current_TimeStamp
- Day_Of_Quarter
- DayName
- DayOfMonth
- DayOfWeek
- DayOfYear
- Hour
- Minute
- Month
- Month_Of_Quarter
- MonthName
- Now
- Quarter_Of_Year
- Second
- TimestampAdd
- TimestampDiff
- Week_Of_Quarter
- Week_Of_Year
- Year
Presentation Variables
The only way you can create variables within the presentation side of OBIEE is with the use of presentation variables. They can only be defined by a report prompt. Any value selected by the prompt will then be sent to any references of that filter throughout the dashboard page.
In the prompt:
From the “Set a variable” dropdown, select “Presentation Variable”. In the textbox below the dropdown, name your variable (named “n” above).
When calling this variable in your report, use the syntax @{n}{default}
If your variable is a string make sure to surround the variable in single quotes: ‘@{CustomerName]{default}’
Also, when using your variable in your report, it is good practice to assign a default value so that you can work with your report before publishing it to a dashboard. For variable n, if we want a default of 6 it would look like this @{n}{6}
Presentation variables can be called in filters, formulas and even text boxes.
Dummy Column Prompt
For situations where you would like users to select a numerical value for a presentation variable, like we do with @{n}{6} above, you can convert something like a date field into values up to 365 by using the function DAYOFYEAR(“Time”.”Date”).
As you can see we are returning the SQL Choice List Values of DAYOFYEAR(“Time”.”Date”) <= 52. Make sure to include an ORDER BY statement to ensure your values are well sorted.
