Packt+ | Advance your knowledge in tech

You're reading from Oracle Business Intelligence Enterprise Edition 12c - Second Edition

Product typeBook

Published inApr 2017

PublisherPackt

ISBN-139781786464712

Edition2nd Edition

Tools

Oracle Enterprise Manager

Concepts

Business Intelligence

Authors (3):

Adrian Ward

Christian Screen

Haroun Khan

View More author details

Chapter 15. Reporting Databases

"It's all about the database" - Anonymous.

No book on Oracle BI would be complete without introducing the concepts of a reporting database.

Just to be clear, Oracle Business Intelligence Enterprise Edition (OBIEE) is neither a database nor a storage system for data. OBIEE grabs data from a source (which is normally a database), and sends it to your screen (or e-mail, PDF, and so on) in a presentable format.

As discussed in previous chapters, the source data can be held in a variety of formats including, but not limited to, spreadsheets, tables, and XML. However, for most large implementations, a database is the only suitable source. If there are millions or even billions of data items, then a database is crucial. Moreover, a well-structured and well-maintained database is essential for the very survival of an OBIEE project. Size matters when it comes to design: the bigger the database, the better the design needs to be; otherwise it will be impossible for your...

Theories and models

It is said that creating a database is more about art than it is about science. I tend to agree with this. However, a number of theories and rules have evolved over the last 40 years that are worth understanding before attempting to build a database for an Oracle Business Intelligence system.

From an overall design perspective, there are two scientific types of database:

Transactional databases
Reporting databases

A transactional database is designed for the input and update of data, usually in small, high volume changes to the data; whereas a reporting database is designed for fast access to data, which can be transformed into useful information for decision-making. The common name for a reporting database is the data warehouse (a phrase originally coined by Bill Inmon, the inventor of data warehousing).

The following diagram shows how tables in a transactional model are laid out. It shows a small extract of the system that will be used throughout this book, and is based...

Designing your database - objectives, rules, and goals

Whenever you create a data warehouse for reporting, you have to consider that there are finite resources. There is never enough space to store data, never enough time to populate the database, and never enough processing power to use a fully normalized source. Even with the latest super-fast technologies, such as Oracle exadata, there is a limit to the amount of data that can be stored or processed in a given time period.

The primary objective should always be to speed up report production, which means using a dimensional model, particularly when storing a large amount of data. As discussed in the theories section, using an Entity Relational database would reduce the redundancy of data, and therefore reduce the amount of data stored and the time taken to load it; however, report production times would increase.

It is always necessary to make a trade-off between Data Volume (to increase the speed of reporting), Load Speed (to minimize the...

Creating a warehouse

This section of the chapter will lead you through the design and build process for the small warehouse (often referred to as a data mart) used for the reporting examples in the following chapters of this book.

For this book, we have taken the Microsoft AdventureWorks sample system, which already includes a warehouse schema for reporting.

Therefore, in this next section I will use a theoretical tennis statistic reporting system to show you the steps involved in designing a data warehouse.

The first step is to assess each source system table for its type of data in order to determine if it fits into a Dimension table, a Fact table, or another table type. Based upon our assessment of the source tables, we can then design and build the warehouse tables. This is followed by the creation of a process to copy the data from the source to the warehouse. Finally, we review and tune the database in order to ensure that we can meet the goals we have set.

Source system assessment

We will...

Some definitions

A review - what you should know now!

How a reporting database needs to be structured
You need to set objectives when you create a warehouse (for example, the ETL should run in 3 hours)
What the rules are for creating efficient warehouses
The goals you can use when designing a warehouse
The steps involved in creating a warehouse from a source database

Summary

In this chapter, we introduced the basic concepts of database design, and which ones are more suited to reporting. We have also seen how a balance between report speed and data loading needs to be struck, given limited resources.

To ensure that your database is useful to its customers, it should achieve certain objectives which are met using rules and goals that you lay down in the initial design phase and follow up in each design choice.

Implementing a database does not stop when the objects are created, but should be followed up with a continuous monitoring and maintenance process.

In the production of this book, we have created a small warehouse for reporting on Tennis Statistics. All the database objects and data are available for download from the book's website.

The rest of the chapter is locked

You have been reading a chapter from

Oracle Business Intelligence Enterprise Edition 12c - Second Edition

Published in: Apr 2017Publisher: PacktISBN-13: 9781786464712

A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.

undefined

Unlock this book and the full library FREE for 7 days

Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of

Start free trial

Renews at $15.99/month. Cancel anytime

Authors (3)

Adrian Ward

Adrian Ward is an Oracle ACE Associate who started working in Siebel Analytics back in 2001 and quickly realized the potential in the technology. He formed the UK's first independent consultancy focusing purely on OBIEE (nee Siebel Analytics) and Oracle BI Applications. He has led many large successful OBIEE implementations in a wide range of business sectors, from investment banking to military operations. His deep technical OBIEE and BI Applications knowledge has been applied on dozens of projects throughout the globe, including HR, Sales, Service, Pharma, and Custom Analytics, which is enabling hundreds of thousands of users in their day-to-day roles. He was also one of the first bloggers on Oracle BIEE at http://www.obiee.info and today runs the Addidici OBIEE consultancy, which has operations in the UK, Europe, and South Africa. Adrian runs one of the largest Oracle BI networking groups on LinkedIn -- Oracle Business Intelligence, is an active tweeter (@Addidici), is a speaker at Oracle conferences, and helps others learn. In his spare time, he loves sailing at Hayling Island, skiing, enjoying life with his family, and learning new technologies.
Read more about Adrian Ward

Christian Screen

Christian Screen (@christianscreen) is an Oracle ACE, technologist, and Business Intelligence evangelist with over 20 years of experience in technology ranging from low-level programming, e-commerce, Data Warehousing, Hyperion Enterprise Performance Management, product management, IoT, and of course, analytics. Founder of Art of BI Software and Consulting Group, one of Oracles top Oracle Analytics partners in North America, his company was acquired by Datavail (@datavail) Corporation in 2016. In his spare time, he enjoys writing technical articles, learning new technologies, inventing new products, writing software, spending time with his family, trying to change the world, and running his blog and podcast which are read and heard all across the globe.
Read more about Christian Screen

Haroun Khan

Haroun Khan is one of Europe's leading OBIEE consultants. A computer science graduate of Imperial College, London, he has been involved with OBIEE from its early days as an acquisition from nQuire by Siebel, and subsequently as part of the Oracle family. Haroun worked as a consultant on projects worldwide for Siebel and as a Principal Consultant for Oracle over a period of 10 years. He has specialized in BI and data warehousing over a longer period including time working at MicroStrategy. Haroun is also an entrepreneur, successfully founding and currently running the online travel site https://www.jrpass.com/. His experience in e-commerce has given him new insight into how analytics is vital to the running of any business nowadays. He engages with cohort analysis, clickstream analytics, and conversion tracking. He still freelances in leading and designing projects in the traditional BI and data warehousing space. In his downtime, Haroun likes to climb, is an avid squash player, and can sometimes be found prone, deep in despair, as he tries his hands at writing a novel.
Read more about Haroun Khan

Personalised recommendations for you

Based on your interests and search pattern

Et al.

Ever wonder why speech recognition systems don't understand the Scottish accent, or what would happen if an astronaut only ate mac 'n' cheese, or other spurious reflections you'd have at a bar? We did, then collated those deliberations into absurd research articles with fake figures and methodologies inspired by even more fictionally absurd studies.

BookAug 2023230 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages4

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages1

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Mastering Tableau 2023

This book is a comprehensive resource to mastering your Tableau skills and becoming a BI expert. As you progress, you will learn how to build advanced dashboards and improve your storytelling to derive key business insight, as well as make you well-versed with advanced functionalities of Tableau in the business intelligence domain.

BookAug 2023684 pages

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages5

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages2

Data Engineering with AWS

Embark on a journey to master data engineering pipelines on AWS! Our book offers a hands-on experience of AWS services for ingesting, transforming, and consuming data. Whether you're an absolute beginner or someone with basic data engineering experience, this guide is an indispensable resource.

BookOct 2023636 pages5

Modern Data Architecture on AWS

Every organization wants an agile, performant, and cost-effective data platform that meets all their current and future business needs. Purpose-built AWS analytics services and their features play a big part in building such a modern data platform. This book brings to you all the design and architectural patterns that’ll help you achieve this goal.

BookAug 2023420 pages5

Practical Guide to Applied Conformal Prediction in Python

Discover the power of Conformal Prediction with the "Practical Guide to Applied Conformal Prediction in Python." Master the latest techniques to quantify uncertainty in machine learning and computer vision models, and seamlessly apply them to your industry applications.

BookDec 2023240 pages

TinyML Cookbook

With over 70 project-based recipes, the TinyML Cookbook is a practical guide that will help you to get the most out of your microcontrollers. It provides a comprehensive understanding of the theoretical foundations while giving you hands-on experience training ML models for deployment on Arduino Nano 33 BLE Sense, Raspberry Pi Pico, and SparkFun RedBoard Artemis Nano microcontrollers.

BookNov 2023664 pages

Acronyms	Definitions
ETL	Extract, Transform, and Load: The process of taking data from a source system into a warehouse.
SQL	Structured Query Language: The basic language that databases such as Oracle and SQL Server understand. SQL is used to create data, create database objects such as tables, and to query data.
Performance Data	I use this term to describe any data which helps to speed up reporting performance. This includes creating aggregate tables, for example the Tournament Fact table. It also includes the use of subsets of data, partitioning of tables, and indexing.
Star Transformation	A special feature of Oracle databases is their ability to maximize the performance of a query that uses a star design table layout. Oracle can build a more efficient query than usual due to the fact that it knows all Dimensions join to the Fact table.
Hints	Hints are used to help an Oracle database to query the tables in the most efficient manner. This could include...