Thanks for the article. Thanks in advance. Post a Comment. The most powerful, open Analytic Engine.
|Published (Last):||18 February 2009|
|PDF File Size:||6.66 Mb|
|ePub File Size:||16.68 Mb|
|Price:||Free* [*Free Regsitration Required]|
Recall that an Oracle OLAP analytic workspace contains a collection of dimensions and a collection of cubes, where any given cube contains only the dimensions required to describe the measures in that cube. The analytic workspace also holds other multidimensional objects, such as folders and programs, that are required for an OLAP analysis.
The article starts with an overview of the components and files used in the demonstration. During the demonstration, we walk you through how to prepare data, create an analytic workspace, create and populate dimensions, and create and populate cubes. Our goal for this demonstration is to provide an overall workflow and some best practices to get you started with Oracle OLAP.
You can use the same workflow to build both simple and complex OLAP models. This walk-through is for demonstration purposes only. In this demonstration, we create an analytic workspace using a four-step process:.
After we show how to create a cube with dimensions and some simple calculations, we discuss adding more advanced calculations and operations to dimensions and cubes. In the architecture diagrams for Oracle OLAP, we introduced the key components involved in the building process.
Figure You can build analytic workspaces using the administration tools. For this article's example, we use AWM. If you want to use OWB, you will find the processes and concepts to be similar, although the user interface is different.
You can download the sample data from the Oracle Press web site. In this section, we describe how to use AWM to build an analytic workspace and the objects contained within. After an overview of AWM, we walk you through the four steps required to build and populate OLAP cubes, as noted in the previous section. The first time you start AWM, you are asked to create a database connection. Your Oracle Database Administrator can supply this information. After you connect to the database, you are prompted to log in to the database.
If you like, you can configure your AWM environment at this time. The following sections describe the steps to get started with AWM. You can configure AWM to suit your environment and preferences. The configuration settings affect the way that AWM behaves.
To set your configuration preferences, select Tools Configuration. We recommend setting the following options:. You can specify other options as desired. For descriptions of the options, click Help. If you are building 10g mode AWs with either Oracle Database 10g or Oracle Database 11 g in 10g mode, and you want to use SQL to access cubes, you need to obtain and install the plug-in that enables the generation of SQL views.
Before we get started building the analytic workspace, here is one more helpful tip. Normally, when you attach an analytic workspace, it comes up read-write.
This behavior can be changed by modifying the awm. By adding a setting, you can have AWM prompt you for how to open the analytic workspace. This will give you the option to attach the workspace as read-only, which is useful when you want to explore an analytic workspace without the danger of inadvertently modifying it. The next time you attach an analytic workspace, you will be prompted to choose how you want to open it: Read Only, Read Write, or Read Write Exclusive. If you want to save your work, do not select Read Only.
Before we jump into building the OLAP analytic workspace, we need to start with some data. In this demonstration, we are working with a relational fact table that contains daily sales data for a computer sales company. The fact table has four lookup tables, as shown in Figure In Figure , the All level is represented explicitly in the source data. This single value represents the top of the hierarchy and it is repeated in every data row. In Oracle OLAP 11 g, you do not need to include it and can enjoy some space savings by leaving it out.
Version 11 g also provides the ability to enter equations in the mapping panel, so that you can calculate source values on the fly from other data without physically storing it.
This gives a mechanism for handling missing values. Many reporting tools, including OBIEE, require an All level for user-defined dimensions, so it is a good idea to include this level where possible. These data sources can be represented as a star schema as in Figure or a snowflake schema as in Figure , as parent-child relations, or as a collection of tables and views. You can also use flat files as data sources, however, the flat files need to be represented by external tables or loaded into tables via an ETL process.
It is possible to use gateways to non-Oracle data as well. If a dimension has skip-level or ragged hierarchies, consider using a snowflake schema to represent the data source. AWM automatically handles the relationships between levels in a snowflake schema. When preparing the dimension table or view, you need to ensure that each member has a key value and description.
If you are using a star schema, the dimension sources must have the full parentage in each row. As you can see, the snowflake tables represent the same data as the star table. The same data is spread across three tables linked with parent keys. To support skip and ragged levels, the parent keys can be any parent key above the child level in any table. Lastly, a parent-child table represents a value-based hierarchy with columns for the member key, parent key, and member description.
Now we are ready to create an analytic workspace that will hold our OLAP dimensions and cubes. Before we get started, let's review some best practices for creating workspaces and naming the metadata used within the workspace. Following the advice in this section will help you to create cubes that are easy to understand and use.
The naming conventions that we suggest ensure that the generated column names in your views are easy to read, and reduce the chances that generated column names will be truncated to fit within the limits for a column name in the Oracle Database. These naming conventions also make it easier and quicker to map to the columns in AWM, because the screens are less cluttered with long object names. Adding prefixes makes names too long and harder to read.
The database knows which objects are which, and you can query the analytic workspace if needed. For example, to find the names of all the dimensions in an analytic workspace, you can use a simple SQL command like the following:. Table suggests some possible names for common object types and provides guidelines for the number of characters to use for each type of object. The dimension on the right side implements the preceding best practice guidelines.
Figure shows the same dimensions in a different view. Notice that in this view, some column names have been truncated due to the maximum length of column names in the database. For the poor names, the truncation makes things even more unreadable.
As you can see, the shorter names make for easier SELECT statements, which are also easier to read and may actually run faster. Long and short description attributes are created automatically. You can change them if shorter names are desired. Analytic workspaces can become very big, and the maintenance of the analytic workspaces is very disk-intensive.
If your analytic workspace will be large, we recommend that you create a separate tablespace to store the analytic workspace. Depending on the update frequency and the size of each update, it may be appropriate to disable logging on this tablespace.
Names are easier to read when you follow best practices. Truncation can cause readability issues. Specify a name for the analytic workspace. Select the tablespace where you want to store the analytic workspace. For this example, use the default tablespace. Click Create. The analytic workspace is created and displayed in the main AWM window. You can create your dimensions in any order.
AWM will list them in alphabetical order once they are created. Remember that dimensions are created once in the analytic workspace and are reused in the cubes.
Dimensions require a default hierarchy and may contain more than one hierarchy. You need to create all dimensions for a cube before you can create the cube itself. The process consists of the following steps:. Oracle OLAP has two basic types of dimensions: user-defined and time. User-defined dimensions represent a majority of the dimensions used by the cubes. Time dimensions are specialized dimensions that have additional characteristics that allow for time-series analysis.
If the application or the cube does not require time-series analysis, you do not need to create a time dimension. TABLE The two types of hierarchies are level-based and value-based.
Level-based hierarchies, the default, require at least one level. Value- based hierarchies do not require levels; you simply give the hierarchy a name and select the Value Based Hierarchy option in AWM. Oracle OLAP requires that all keys for a dimension be unique across all levels and hierarchies within that dimension. They do not need to be unique across all dimensions; for example, you can have a customer and a product If they are not unique within a dimension, the data loader will not load the dimension members during the maintenance process.
Oracle Database 11g: Olap Essentials
Recall that an Oracle OLAP analytic workspace contains a collection of dimensions and a collection of cubes, where any given cube contains only the dimensions required to describe the measures in that cube. The analytic workspace also holds other multidimensional objects, such as folders and programs, that are required for an OLAP analysis. The article starts with an overview of the components and files used in the demonstration. During the demonstration, we walk you through how to prepare data, create an analytic workspace, create and populate dimensions, and create and populate cubes.
What's New in Oracle OLAP?
I have an Oracle 10g Database with relational tables in it. I want to create dimensions and cubes from that data. Do I need to create separate dimension and fact tables from that relational data, or do I just use the tables from the relational tables?? I am VERY confused about all of the documentation. It's not helping me much. This is an interesting question, and probably one that many people have had at some point or other. Part of the confusion is due to the way that OLAP has developed within the Oracle database over the years, and therefore it's probably a good idea to take a bit of a history lesson.
Building an Oracle OLAP Analytic Workspace