lesforgesdessalles.info Laws ORACLE WAREHOUSE BUILDER 11GR2 GETTING STARTED 2011 PDF

Oracle warehouse builder 11gr2 getting started 2011 pdf

Wednesday, January 2, 2019 admin Comments(0)

Extract, Transform, and Load data to build a dynamic, operational data warehouse with Oracle Warehouse Builder 11g R2 with this book and eBook Build a. P U B L I S H I N G professional expertise distilled Oracle Warehouse Builder 11gR2: Getting Started Bob Griesemer Chapter No.3 "Designing the Target. 年4月20日 There's a revised edition of the Bob Griesemer OWB book from Packt Publishing - Oracle Warehouse Builder 11gR2: Getting Started


Author: CASSI THOMBS
Language: English, Spanish, French
Country: Guyana
Genre: Lifestyle
Pages: 216
Published (Last): 03.07.2016
ISBN: 196-6-71047-210-7
ePub File Size: 22.52 MB
PDF File Size: 13.54 MB
Distribution: Free* [*Regsitration Required]
Downloads: 34553
Uploaded by: VANETTA

Oracle Warehouse Builder 11g: Getting Started. Copyright © .. We will build a basic data warehouse using Oracle Warehouse Builder. It has lesforgesdessalles.info, covers each of these. Oracle Warehouse Builder 11gR2: Getting Started Extract, Transform, and Load data to build a dynamic, operational data warehouse Bob Griesemer. Oracle Warehouse Builder 11gR2: Getting Started Extract, Transform, and Load data to build a dynamic, operational data warehouse. Bob Griesemer. 1.

We'll be designing dimensionally and implementing relationally, so let's now design our actual dimensions that we'll need for our ACME Toys and Gizmos data warehouse, and talk about some issues with the fact data or cube that we'll need. In the relational model, the fact table would contain columns for the dollar amount, the quantity, the unit cost, and then foreign keys for each of the dimension tables. Penetration Testing. It includes detailed descriptions of implementing a JDBC connection to an external database and the implementation of a code template mapping to access it. Getting Started So let's take a look at this concept of a cube with dimensions, and how we can use that to represent our data. For this reason, a data warehouse dimensional design that is represented relationally in the database will have one main table to hold the primary facts, or measures we want to store, such as count of items sold or dollar amount of sales.

Keeping objects synchronized as changes are made is discussed, and so is the autobinding of tables to dimensional objects. Lastly, some additional online references are provided for further study and reference. It includes detailed descriptions of implementing a JDBC connection to an external database and the implementation of a code template mapping to access it.

It includes discussion of the main code templates provided by default with OWB 11gR2 and describes everything you need to know to implement your first code template mapping. But before we can do anything with them, we need to design what our target data warehouse structure is going to look like. When we have that figured out, we can start mapping data from the source to the target.

So, let's design our target structure. First, we're going to take a look at some design topics related to a data warehouse that are different from what we would use if we were designing a regular relational database. We'll then discuss what our design will look like, and after that we'll be ready to move right into creating that design using the Warehouse Builder in the next chapter. The specific topics we'll discuss in this chapter include the following: This is a way of looking at the data from a business perspective that makes the data simple, understandable, and easy to query for the business end user.

It doesn't require a database administrator to be able to retrieve data from it. When looking at the source databases in the last chapter, we saw a normalized method of modeling a database. A normalized model removes redundancies in data by storing information in discrete tables, and then referencing those tables when needed. This has an advantage for a transactional system because information needs to be entered at only one place in the database, without duplicating any information already entered.

For example, in the ACME Toys and Gizmos transactional database, each time a transaction is recorded for the sale of an item at a register, a record needs to be added only to the transactions table.

In the table, all details regarding the information to identify the register, the item information, and the employee who processed the transaction do not need to be entered because that information is already stored in separate tables. The main transaction record just needs to be entered with references to all that other information.

This works extremely well for a transactional type of system concerned with daily operational processing where the focus is on getting data into the system. However, it does not work well for a data warehouse whose focus is on getting data out of the system. Users do not want to navigate through the spider web of tables that compose a normalized database model to extract the information they need.

Therefore, dimensional models were introduced to provide the end user with a flattened structure of easily queried tables that he or she can understand from a business perspective. Dimensional design A dimensional model takes the business rules of our organization and represents them in the database in a more understandable way. A business manager looking at sales data is naturally going to think more along the lines of "How many gizmos did I sell last month in all stores in the south and how does that compare to how many I sold in the same month last year?

In the last chapter, we saw how many tables would have to be joined together in such a query just to be able to answer a question like the one above. A dimensional model removes the complexity and represents the data in a way that end users can relate to it more easily from a business perspective.

So let's take a look at this concept of a cube with dimensions, and how we can use that to represent our data. Cube and dimensions The dimensions become the business characteristics about the sales, for example: A time dimension users can look back in time and perform time series analysis, such as how a quarter compares to the same quarter last year A store dimension information can be retrieved by store and location A product dimension various products for sale can be broken out Think of the dimensions as the edges of a cube, and the intersection of the dimensions as the measure we are interested in for that particular combination of time, store, and product.

A picture is worth a thousand words, so let's look at what we're talking about in the following image: How about a Rubik's Cube? We're doing a data warehouse for a toy store company, so we ought to know what a Rubik's cube is! If you have one, maybe you should go get it now because that will exactly model what we're talking about. Think of the width of the cube, or a row going across, as the product dimension.

Every piece of information or measure in the same row refers to the same product, so there are as many rows in the cube as there are products. Think of the height of the cube, or a column going up and down, as the store dimension. Every piece of information in a column represents one single store, so there are as many columns as there are stores.

Finally, think of the depth of the cube as the time dimension, so any piece of information in the rows and columns at the same depth represent the same point in time.

The intersection of each of these three dimensions locates a single individual cube in the big cube, and that represents the measure amount we're interested in.

In this case, it's dollar sales for a single product in a single store at a single point in time. But one might wonder if we are restricted to just three dimensions with this model.

After all, a cube has only three dimensions length, width, and depth. Well, the answer is no. We can have many more dimensions than just three. In our ACME example, we might want to know the sales each employee has accomplished for the day.

This would mean we would need a fourth dimension for employees. But what about our visualization above using a cube? How is this fourth dimension going to be modeled? And no, the answer is not that we're entering the Twilight Zone here with that "dimension not only of sight and sound but of mind If we think of an individual intersection of the three dimensions of the cube as being another cube, we can see that we've just opened up another three dimensions to use the three for that inner cube.

The Rubik's Cube example used above is good because it is literally a cube of cubes and illustrates exactly what we're talking about. We do not need to model additional cubes. The concept of cubes within cubes was just to provide a way to visualize further dimensions. We just model our main cube, add as many dimensions as we need to describe the measures, and leave it for the implementation to handle.

This is a very intuitive way for users to look at the design of the data warehouse. When it's implemented in a database, it becomes easy for users to query the information from it.

Now before we finalize our model for the ACME Toys and Gizmos data warehouse, let's look at the implementation of the model to see how it gets physically represented in the database. There are two options: The relational implementation, which is the most common for a data warehouse structure, is implemented in the database with tables and foreign keys. The multidimensional implementation requires a special feature in a database that allows defining cubes directly as objects in the database.

Let's discuss a few more details of these two implementations. But we will look at the relational implementation in greater detail as that is the one we're going to use throughout the remainder of the book for our data warehouse project. The diagrams presented showed all the tables interconnected, and we discussed the use of foreign keys in a table to refer to a row in another table. That is fundamentally a relational database. The term relational is used because the tables in it relate to each other in some way.

We can't have a POS transaction without the corresponding register it was processed on, so those two relate to each other when represented in the database as tables. For a relational data warehouse design, the relational characteristics are retained between tables.

But a design principle is followed to keep the number of levels of foreign key relationships to a minimum. It's much faster and easier to understand if we don't have to include multiple levels of referenced tables.

For this reason, a data warehouse dimensional design that is represented relationally in the database will have one main table to hold the primary facts, or measures we want to store, such as count of items sold or dollar amount of sales. It will also hold descriptive information about those measures that places them in context, contained in tables that are accessed by the main table using foreign keys.

Book: Oracle Warehouse Builder 11g R2: Getting Started 2011

The important principle here is that these tables that are referenced by the main table contain all the information they need and do not need to go down any more levels to further reference any other tables. The main table in the middle is referred to as the fact table because it holds the facts, or measures that we are interested in about our organization.

This represents the cube that we discussed earlier. The tables surrounding the fact table are known as dimension tables. These are the dimensions of our cube.

These tables contain descriptive information, which places the facts in a context that makes them understandable. We can't have a dollar amount of sales that means much to us unless we know what item it was for, or what store made the sale, or any of a number of other pieces of descriptive information that we might want to know about it.

It is the job of data warehouse design to determine what pieces of information need to be included. We'll then design dimension tables to hold the information. Using the dimensions we referred to above in our cube discussion as our dimension tables, we have the following diagram that illustrates a star schema: Of course our star only has three points, but with a much larger data warehouse of many more dimensions, it would be even more star-like.

Keep in mind the principle that we want to follow here of not using any more than one level of foreign key referencing. As a result, we are going to end up with a de-normalized database structure. We discussed normalization back in Chapter 2, which involved the use of foreign key references to information in other tables to lessen the duplication and improve data accuracy.

For a data warehouse, however, the query time and simplicity is of paramount importance over the duplication of data. As for the data accuracy, it's a read-only database so we can take care of that up front when we load the data.

Oracle Warehouse Builder 11gR2: Getting Started - PDF

For these reasons, we will want to include all the information we need right in the dimension tables, rather than create further levels of foreign key references. This is the opposite of normalization, and thus the term de-normalized is used. Every product in our stores is associated with a department. If we have a dimension for product information, one of the pieces of information about the product would be the department it is in.

In a normalized database, we would consider creating a department table to store department descriptions with one row for each department, and would use a short key code to refer to the department record in the product table. However, in our data warehouse, we would include that department information, description and all, right in the product dimension.

This will result in the same information being duplicated for each product in the department. What that buys us is a simpler structure that is easier to query and more efficient for retrieving information from, which is key to data warehouse usability.

The extra space we consume in repeating the information is more than paid for in the improvement in speed and ease of querying the information. That will result in a greater acceptance of the data warehouse by the user community who now find it more intuitive and easier to retrieve their data. In general, we will want to de-normalize our data warehouse implementation in all cases, but there is the possibility that we might want to include another level basically a dimension table referenced by another dimension table.

In most cases, we will not need nor want to do this and instances should be kept to an absolute minimum; but there are some cases where it might make sense. This is a variation of the star schema referred to as a snowflake schema because with this type of implementation, dimension tables are partially normalized to pull common data out into secondary dimension tables.

The resulting schema diagram looks somewhat like a snowflake.

Getting oracle 2011 started builder pdf 11gr2 warehouse

The secondary dimension tables are the tips of the snowflake hanging off the main dimension tables in a star schema. In reality, we'd want at the most only one or two of the secondary dimension tables; but it serves to illustrate the point. A snowflake dimension table is really not recommended in most cases because of ease-of-use and performance considerations, but can be used in very limited circumstances.

The Kimball book on Dimensional Modeling was referred to at the beginning of Chapter 2. This book discusses some limited circumstances where it might be acceptable to implement a snowflake design, but it is highly discouraged for most cases. Let's now talk a little bit about the multidimensional implementation of a dimensional model in the database, and then we'll design our cube and dimensions specifically for the ACME Toys and Gizmos Company data warehouse.

It also provides advanced calculation and analytic content built into the database to facilitate advanced analytic querying. Oracle's Essbase product is one such database and was originally developed by Hyperion. Oracle recently acquired Hyperion, and is now promoting Essbase as a tool for custom analytics and enterprise performance management applications.

This is an option organizations can leverage to make use of their existing database. These kinds of analytic databases are well suited to providing the end user with increased capability to perform highly optimized analytical queries of information. Therefore, they are quite frequently utilized to build a highly specialized data mart, or a subset of the data warehouse, for a particular user community.

The data mart then draws its data to load from the main data warehouse, which would be a relational dimensional star schema. A data warehouse implementation may contain any number of these smaller subset data marts. We'll be designing dimensionally and implementing relationally, so let's now design our actual dimensions that we'll need for our ACME Toys and Gizmos data warehouse, and talk about some issues with the fact data or cube that we'll need.

This will make the concepts we just discussed more concrete, and will form the basis for the work we do in the rest of the book as we implement this design. We'll then close out this chapter with a discussion on designing in the Warehouse Builder, where we'll see how it can support either of these implementations. We have seen the word dimension used in describing both a relational implementation and a multidimensional implementation.

It is even in the name of the second implementation method we discussed, so why does the relational method use it also? In the relational case, the word is used more as an adjective to describe the type of table taken from the name of the model being implemented; whereas in the multidimensional model it's more a noun, referring to the dimension itself that actually gets created in the database.

In both cases, the type of information conveyed is the same descriptive information about the facts or measures so its use in both cases is really not contradictory.

Oracle Warehouse Builder 11g R2: Getting Started 2011

There is a strong correlation between the fact table of the relational model and the cube of the dimensional model, and between the dimension tables of the relational model and the dimensions of the dimensional model. Let's lay out a basic structure of information we want each to contain. We'll begin with the dimensions, since they are going to provide the context for the measure s we will want to store in our cube. Identifying the dimensions To know what dimensions to design for, we need to know what business process we're going to be supporting with our data warehouse.

Is management concerned with daily inventory? How about daily sales volume? This information will guide us in selecting the correct parts of the business to model with our dimensions.

We are going to support the sales managers in managing the daily sales of the ACME Toys and Gizmos Company, and they have already given us an example of the kind of question they want answered from their data warehouse, as we saw earlier. We used that to illustrate the cube concept and to show a star schema representation of it, so the information shows us the dimensions we need. Are we going to need both the time and the date in this dimension, or will just the date be sufficient? We can get an answer to this question by also looking back at our business process, which showed that management is concerned with daily sales volume.

Also, the implementation of the time dimension in OWB does not include the time of day since it would have to include 24 hours of time values for each day represented in the dimension due to the way it implements the dimension. In the future if time is needed, there are options for creating a separate dimension just for modeling time of day values.

For our initial design, we'll call our time related dimension a Date dimension just for added clarity. Each sale transaction is for a particular product, and management has indicated they are concerned about seeing how well each product is selling.

So we will include a dimension that we shall call Product. At a minimum we need the product name, a description of the product, and the cost of the product as attributes of our product dimension so we'll include those in our logical model. So far we have a Date dimension to represent our time series and a Product dimension to represent the items that are sold. We could stop there. Management would then be able to query for sales data for each day for each product sold by ACME Toys and Gizmos, but they wouldn't be able to tell where the sale took place.

Another key piece of information the management would like to be able to retrieve is how well the stores are doing compared to each other for daily sales. Unless we include some kind of a location dimension, they will not be able to tell that.

That is why we have included a third dimension called Store. It is used to maintain the information about the store that processed the sales transaction. For attributes of the store dimension, we can include the store name and address at a minimum to identify each store.

These dimensions should be enough to satisfy the management's need for querying information for this particular business process the daily sales. We could certainly include a large number of other dimensions, but we'll stop here to keep this simple for our first data warehouse. We can now consider designing the cube and what information to include in it. Designing the cube In the case of the ACME Toys and Gizmos Company, we have seen that the main measure the management is concerned about is daily sales.

There are other numbers we could consider such as inventory numbers: How much of each item is on hand?

However, the inventory is not directly related to daily sales and wouldn't make sense here. We can model an inventory system in a data warehouse that would be separate from the sales portion. But for our purposes, we're going to model the sales.

Therefore, our main measure is going to be the dollar amount of sales for each item. A very important topic to consider at this point is what will be the grain of the measure the sales data that we're going to store in our cube?

The grain or granularity is the level that the sales number refers to. Since we're using sales as the measure, we'll store a sales number; and from our dimensions, we can see that it will be for a given date in a given store and for a given product.

Will that number be the total of all the sales for that product for that day? In today's economy, businesses and IT professionals cannot afford to lag behind the latest technologies. Data warehousing is a critical area to the success of many enterprises, and Oracle Warehouse Builder is a powerful tool for building data warehouses.

It comes free with the latest version of the Oracle database. Written in an accessible, informative, and focused manner, this book will teach you to use Oracle Warehouse Builder to build your data warehouse. Covering warehouse design, the import of source data, the ETL cycle and more, this book will have you up and running in next to no time.

This book will walk you through the complete process of planning, building, and deploying a data warehouse using Oracle Warehouse Builder.

By the book's end, you will have built your own data warehouse from scratch. Kali Linux. Machine Learning. Mobile Application Development. Penetration Testing. Raspberry Pi. Virtual and Augmented Reality.

NET and C. Cyber Security. Full Stack. Game Dev. Git and Github. Technology news, analysis, and tutorials from Packt. Stay up to date with what's important in software engineering today. Become a contributor. Go to Subscription. You don't have anything in your cart right now. In today's economy, businesses and IT professionals cannot afford to lag behind the latest technologies. Data warehousing is a critical area to the success of many enterprises, and Oracle Warehouse Builder is a powerful tool for building data warehouses.

It comes free with the latest version of the Oracle database. Written in an accessible, informative, and focused manner, this book will teach you to use Oracle Warehouse Builder to build your data warehouse. Covering warehouse design, the import of source data, the ETL cycle and more, this book will have you up and running in next to no time.

This book will walk you through the complete process of planning, building, and deploying a data warehouse using Oracle Warehouse Builder. By the book's end, you will have built your own data warehouse from scratch.

Starting with the installation of the Oracle Database and Warehouse Builder software, this book then covers the analysis of source data, designing a data warehouse, and extracting, transforming, and loading data from the source system into the data warehouse. You'll follow the whole process with detailed screenshots of key steps along the way that have all been updated for the new Fusion Client Platform interface in 11gR2, alongside numerous tips and hints not covered by the official documentation.

Sign up to our emails for regular updates, bespoke offers, exclusive discounts and great free content. Log in.

Hm... Are You a Human?

My Account. Log in to your account. Not yet a member?

Started 11gr2 warehouse oracle builder pdf getting 2011