Data Warehousing and Data Science

7 January 2011

Introduction to Oracle BI for MS BI Developer

Filed under: Oracle — Vincent Rainardi @ 8:15 pm
Tags: ,

If you are a MS BI developer or Architect (SS, IS, RS, AS) you need to look at Oracle BI. a) so that we know what’s out there, and b) we increase our understanding of our own area by studying other areas. When I ask people which BI stack did you implement? These days the answer is almost always MS BI. There other BI suites: SAS, SAP (BW & BO), IBM (Cognos + InfoSphere) and Oracle. Out of these, OBI is not the most popular (BO or Cognos probably is), but OBI is the “latest trend”, i.e. with the arrival of OBIEE 11g, in the 2nd half of 2010 people start considering OBI as an alternative to MSBI.

The documentation for OBIEE in general is here. Release 11g is here. Release 10g is here.

History

The OBIEE 11g originated from Siebel Analytics (latest version was 7.8, Jan 2006). Answers, Interactive Dashboards, and Delivers were from Siebel Analytics (read here). Discoverer was not from Siebel Analytics, it was from Fusion Middleware (see this book page 31), along with Forms, Reports and Portal. At that time, OBI was part of “Oracle Fusion Middleware 11g Release 1”, see here. Apart from OBI, OFM 11g R1 consists of Communication Services, Identity Management, SOA Suite, WebCenter, WebLogic, HTTP Server, and Application Development Framework.

So, in Jan 2006, OBI is located in 2 places: a) Siebel Analytics contains Answers, Delivers and Intelligent Dashboards, b) OFM 11g R1 contains Discoverers, Forms, Reports and Portal.

Then OBI 10g was released in June 2006 (10.1.3.2). It consisted of Answers, Delivers, Interactive Dashboards, BI Publisher, Disconnected Analytics. Discoverer 10g was a separate product, released in Dec 2004 (10.1.2, aka Drake), see this book page 4. Discoverer was a very mature product, has been in the market for a long time, first released April 1997. Other Oracle BI tools: Beans, Reports, Data Miner, OWB, Spreadsheet Add-In were all separate tools, not in the “OBI 10g” suite.

OBIEE 11g was released in July 2010. 10g’s Answers is renamed as Analysis. 10g’s Interactive Dashboards is renamed as Dashboards. 10g’s Delivers is renamed as Agents. New features added: Scorecards, KPI, Actions, Conditions, Filters, Prompts and OBI Add-in for MS Office. BI Publisher (Report Designer) and Real-Time Decision are also part of OBIEE 11g.

Components

I’m going to use 11g terms here, instead of 10g.

An Analysis is a DW/BI SQL query presented in the form of tables, pivot tables and charts. These tables and charts can then be included in a Dashboard. We can create a prompt in an Analysis, to allow the users to select a value to filter the result of the Analysis. We can integrate an Analysis with an Excel Internet Query (IQY) to enable us to run the Analysis from Excel. We can create an Agent from an Analysis to schedule the Analysis to run at certain time. We can use variables practically anywhere within an Analysis (title, column, formula, formatting conditions, SQL, header, etc).

A Dashboard is a collection of analysis. A Dashboard consists of one page or several pages (shown as “tabs” on the top of the Dashboard). Apart from displaying Analysis, a Dashboard page can also display images, text, alerts, action links, views, reports, URL links and embedded objects. There is a toolbar on the top right of the Dashboard containing buttons such as: Edit, Print, etc. We can create a Skin to change the appearance of a Dashboard, e.g. background colour, logo and style sheets. A Skin can be assigned to users so each user can have a different look. A Style control how a Dashboard are formatted, e.g. the colour, font & size of the text, table border, graph attributes. Styles are organised into folders that contains Cascading Style Sheets, images and graph templates. We can create a Dashboard Template, which acts as a starting point for users to build their own Dashboard pages.

A Briefing Book is a collection of Dashboards. A Briefing Book can also contain an individual Analysis and a BI Publisher Report. The Dashboards, Analyses and Reports in a Briefing Book can be static (can’t be updated) or updatable (the content is refreshed everytime we open it). We can export a Briefing Book to PDF. We can deliver a Briefing Book to users using an Agent.

BI Publisher is a tool to create and publish reports. We can read data from databases (using SQL), spreadsheets, Analysis and OLAP DB (using MDX). We can also read from LDAP, Web Services, HTTP feeds, and XML files. If the data source is unrelated (such as individual files), we can create relationships between data sources. Using Publisher we can create layouts for our reports and publish the reports to various output format, including: HTML, PDF, Excel, PowerPoint, XML, CSV, and Rich Text. The output can be sent to: printer, email, file, fax, WebDAV or FTP. We can split a single report into multiple sections, each section is sent to different recipients on multiple destinations, using different formats. This is called Bursting. We can create a Template that contains style information that can be applied to RTF layouts. For PDF output, we can split the PDF into several smaller files (called PDFZ). We can schedule a report to produce multiple outputs, each with different layout, and each is sent to different destinations. There is an interactive viewer, where users can sort or filter a table, scroll the table, get a pop-up graph, and do “propagated filtering” across different area of the report.

Real Time Decision (RTD) is a tool to create/model business processes, gather data/statistics, and make recommendations (all that is called an Inline Service). An Inline Service consists of several elements including: Performance Goals, Choices, Rules, Statistics  Collector and Models. A Performance Goal is KPIs used for setting criteria for the scoring of choices. A Rule is used to target a segment of population. A Rule can also be used to decide whether a choice is eligible. A Model is self-learning and predictive, used for optimising decisions and providing real-time analysis. RTD’ is fully integrated with Eclipse development environment. We can deploy an Inline Service, run it to process requests, monitor its performance, gather and view the statistics, and fine-tune/optimise it.

How these components fits together is shown in OBIEE system architecture diagram here.

 

8 Comments »

  1. Can MSBI be configured with other products like SAP and Oracle?

    Comment by Khallu S — 5 April 2014 @ 3:10 pm | Reply

    • Hi Khallu, I’m not sure what you meant by “can be configured with” (is it: “read from”, “write to”, “used with”? because MSBI as so many components with different functions: ETL, OLAP, DW, DQ)
      And I’m not sure what you meant by “Oracle” (is it: Oracle Database, OBIEE, ODI, there are so many Oracle products)
      And I’m not sure what you meant by “SAP” (is it: the ERP system, SAP BO, SAP Hana, SAP Lumira, there are so many SAP products)
      Because I’m not sure about the above 2 points, here’s my reply:
      SSIS can use both SAP and Oracle as the source system.
      SSIS can write data to Oracle DB as a Data Warehouse or Staging.
      SSAS and SSRS can use Oracle DB as a data source (i.e. the DW is on Oracle DB)
      Oracle Discoverer can use SQL Server DB as a data source.
      ODI can use SQL Server DB as a data source.
      SAP BO, SAP Hana and SAP Lumira can use SQL Server as a data warehouse (i.e the DW is located on SQL Server)

      Comment by Vincent Rainardi — 6 April 2014 @ 10:18 am | Reply

  2. HI i want to know as per job searcher which technology I should go for…MSBI or OBIEE
    Thanks

    Comment by Nirmal — 30 March 2015 @ 1:04 pm | Reply

    • Hi Nirmal, it depends on your personal profile/interest and the job market in your country (which country are you in?).
      Money wise, OBIEE is slightly higher. MSBI in UK with 2-3 years exp in Banking is about £45-50k (£35-40 in Retail sector). OBIEE is £60k. In the UK, MSBI market size is probably about 10x bigger than OBIEE (live job vacancies 600 vs 60, in itjobswatch), but the number of MSBI developers is a lot more than OBIEE developers. But you should put your interest higher than money or market size, because in the long run, it is your interest which determines your success. I would suggest you install and use both of them and find out for yourself which one interest you most. Both technology are quite safe for your job security/career prospects in the long run, as both of them have quite robust market positions, supported by strong development commitment in Microsoft and Oracle. Hope this helps.

      Comment by Vincent Rainardi — 30 March 2015 @ 6:04 pm | Reply

      • Hello Sir Vincent Rainardi,

        Thanks for the reply & also for the current status of technologies on account of job & as well monetary value in the market,I am more dedicated towards Microsoft Technologies rather than Oracle I must choose the MSBI.

        Sir what are the major challenges I will experience in this technology,…..I forget to tell you I belong to Bengaluru/India.
        Thanks

        Comment by Nirmal — 1 April 2015 @ 11:38 am

  3. Hi Nirmal, I’m not a Sir :). The major challenges are:

    1. SSRS, SSIS and SSAS are different technologies. One for reporting, one for ETL and one for cube. You to learn these different technologies, which for some people could be difficult. For example, SSRS has so many different controls over each properties of the objects in report, and it can also read MDX output as well as Relational data sets. And there is report builder too. SSIS has built-in C#.NET/VB.NET scripting capability which for some people not familiar with .NET could be an issue. And there are so many different tasks that we need to get used to. The concept of tuples, sets, members and axes in SSAS could be mind binding for someone new to multidimensional world. And SSAS has a Tabular side too, of which the DAX expressions could take some time to get used to.

    2. Outside these 3 technologies, there are other tools in Microsoft BI Toolset. The most prominent one is Power BI which is PowerPivot, PowerQuery, PowerMap and PowerView, which again takes sometime to learn. And we must not forget SharePoint, the core underlying platform for Microsoft BI, connecting SSRS web parts and subscriptions, hosting PowerQuery, PowerPivot, PowerView, PowerMap, PerformancePoint, and Excel Services. PowerQuery introduces another language for us to learn: M. Excel has a very important role in Microsoft BI, enabling users to access SSAS cubes, as well as creating Pivot Tables, Pivot Chart and using PowerPivot. Not just Excel, but the whole of Microsoft Office is part of the MS BI solution, particularly Office 365.

    3. We must not forget that the core data store in Microsoft BI is the old SQL Server, which, in 2014 has a bunch of important new features very useful to BI, such as: memory-optimized tables, column store technology, as well as Data Warehousing features which improves over the years: CDC, Change Tracking, partitioning, data compression, star join query, bitmap filter, etc. And don’t forget basic, essential, old SQL Server features which we use when building MS BI solutions: Job Agent, Resource Governor, High Availability, indexing, geospatial data, table valued functions, etc. And for many years we are now in the cloud with Azure. A lot of cloud capability are important to Microsoft BI stack: Azure SQL database, Azure Data Factory (compare it with SSIS), HD Insight / big data, Machine Learning (with R language), Azure Document DB, Azure Search (watch Allan Mitchell SQLBits presentation).

    4. Outside the above 3 things, we STILL have other things within Microsoft BI space. Data Quality Services (DQS), Master Data Services (MDS), Visual Studio, Test Data Generator, C# applications (Web and Windows), Light Switch, TFS, Database Project, Continuous Integration. When building a data warehouse for Microsoft BI, these features are used. Either they are to help the development (TFS, CI, DB Proj) or add extra BI features (DQS and MDS). DQS in particular is essential in BI, i.e. Data Cleansing, Data Matching, Reference Data Services, Data Profiling, Notifications. All these means more things to learn for us.

    Compare this with 1998 when people built the ETL using Data Transformation Services (DTS). There’s no HD Insight, Azure, Power BI, and all the above stuff. Nothing, just DTS and SQL Server 7. That’s it. Microsoft BI has gone a huge transformation beyond recognition in the last 15 years. Big quantum leap. But so are other companies. If you look at Cognos BI for example, they have Query Studio, Report Studio, Analysis Studio, Event Studio, Go Office, Go Search, Go Dashboard, TM1, and they are also in the cloud, and IBM has other products for BI and warehousing too: Netezza, DB2, InfoSphere MDM, InfoSphere Warehouse, InfoSphere Identity Insight, InfoSphere Streams, InfoSphere DataStage, InfoSphere CDC, InfoSphere Replication Server, InfoSphere Discovery, PureData for Analytics, Informix Warehouse, and IBM big data platform: BigInsights (Hadoop, BigSheets, BigSQL, Big R, Big Match, HBase, Watson Explorer). The list goes on and on. Really. And if we look at Oracle, they also have lots of BI and Warehousing products. And so does SAP.

    If I have to summarise the challenge of learning Microsoft BI (or any other vendor) these days in one word, I would say “huge”. It is big, wide and deep. This stuff takes a lot of times to learn it all. In fact, it is impossible to learn it all. It is not possible to learn all of the tools within Microsoft BI. There are too many. You need to specialise. But in my opinion, we should only specialise when we have gone through everything. We should gone through the whole width thinly, and then go deep on one area that we want to specialise in.

    Comment by Vincent Rainardi — 2 April 2015 @ 6:05 pm | Reply

    • Hello Vincent,
      Thanks for the reply & no more “Sir” from this time…..

      I was not well so was not able to reply,I must tell,you have got a good grasp on these technologies as per your reply seems.I was intrested in BI but this time you make me even more eager to go after these technologies…..

      Could you suggest me some books at the beginner level to understand SSAS cube model ?

      Thanks Again……
      Nirmal

      Comment by Nirmal — 8 April 2015 @ 8:13 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: