Understanding Oracle OLAP Dimensions And Cubes

转载 2006年05月19日 14:44:00

Understanding Oracle OLAP Dimensions And Cubes

A reader asks:

"I am new to OLAP and Oracle OLAP and I have a question. 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.

Disregarding Oracle Express for the time being, OLAP first became a feature of the Oracle database back with Oracle 8i Enterprise Edition, when some relational OLAP (ROLAP) features were added to the database. Oracle 8i Enterprise Edition came with a database feature known as dimensions, which were an additional layer of metadata you could put over a table or set of tables, to define hierarchical relationships between columns. For example, you could say that one column, 'COUNTRY', was the parent of another column 'REGION', which itself was the parent of another column, 'CITY'. A single dimension could contain multiple hierarchies and the database could contain multiple dimensions, unique within each schema.

Dimensions reference existing tables, and do not contain any data themselves - they merely add additional metadata to existing database objects. For example, to create a product dimension, you'd first create the table that contains the data, and then create the dimension afterwards.

CREATE TABLE products (
prod_id NUMBER(6) NOT NULL,
prod_name VARCHAR2(50 byte) NOT NULL,
prod_desc VARCHAR2(4000 byte) NOT NULL,
prod_subcategory VARCHAR2(50 byte) NOT NULL,
prod_subcat_desc VARCHAR2(2000 byte) NOT NULL,
prod_category VARCHAR2(50 byte) NOT NULL,
prod_cat_desc VARCHAR2(2000 byte) NOT NULL,
prod_weight_class NUMBER(2),
prod_unit_of_measure VARCHAR2(20 byte),
prod_pack_size VARCHAR2(30 byte),
supplier_id NUMBER(6),
prod_status VARCHAR2(20 byte) NOT
NULL,
prod_list_price NUMBER(8, 2) NOT NULL,
prod_min_price NUMBER(8, 2) NOT NULL,
product_total VARCHAR2(13 byte)
);

CREATE DIMENSION products
LEVEL product IS (products.prod_id, products.prod_desc, products.prod_list_price, products.prod_min_price, products.prod_name, products.prod_pack_size, products.prod_status, products.supplier_id, products.prod_unit_of_measure, products.prod_weight_class)
LEVEL subcategory IS (products.prod_subcategory, products.prod_subcat_desc)
LEVEL category IS (products.prod_category, products.prod_cat_desc)
HIERARCHY prod_hier (product CHILD OF subcategory CHILD OF category)
ATTRIBUTE product DETERMINES (products.prod_category, products.prod_id)
ATTRIBUTE subcategory DETERMINES (products.prod_category, products.prod_subcategory)
ATTRIBUTE category DETERMINES products.prod_category;

Dimensions created in this way are then used by the query rewrite mechanism within the Enterprise Edition of the database to perform more complex forms of rewrite - specifically, to allow the rewrite mechanism to aggregate up from summaries at lower levels in a hierarchy to levels higher up. In addition, dimensions help the Oracle 8i summary adviser to recommend materialized views, as the dimension and it's hierarchies define how data 'rolls up' when aggregates are required.

Oracle 9i introduced something called the 'OLAP Option'. The OLAP Option integrated the Oracle Express Server multidimensional engine into the Oracle relational database, and also introduced a further layer of OLAP metadata, known as the OLAP Catalog, together with a Java OLAP API, to provide programmatic and SQL access to OLAP data.

If you use Oracle 9i without the OLAP Option, but you have licensed the Enterprise Edition, you can create dimensions in the same was as with Oracle 8i. As with 8i, you create your tables first, then define your dimensions, which reference columns in the tables. However, if you license the OLAP Option, you now have additional options open to you that can however slightly complicate matters.

One of the key features of the Oracle 9i OLAP Option is that your OLAP data can be stored in either relational tables, or in multidimensional datatypes held within what's termed 'Analytic Workspaces'. Either way, both are accessed using the same Java OLAP API, which in turn decides either to retrieve its data from relational tables or from analytic workspaces, depending on how you've stored the data. Just to complicate matters, analytic workspaces are themselves stored within LOBs in Oracle relational tables, but the way they are created and maintained is quite different to data in relational tables.

With Oracle 9i OLAP Option, to create a relational OLAP dimension, you'd create the table and dimension object as before:

CREATE TABLE products (
prod_id NUMBER(6) NOT NULL,
prod_name VARCHAR2(50 byte) NOT NULL,
prod_desc VARCHAR2(4000 byte) NOT NULL,
prod_subcategory VARCHAR2(50 byte) NOT NULL,
prod_subcat_desc VARCHAR2(2000 byte) NOT NULL,
prod_category VARCHAR2(50 byte) NOT NULL,
prod_cat_desc VARCHAR2(2000 byte) NOT NULL,
prod_weight_class NUMBER(2),
prod_unit_of_measure VARCHAR2(20 byte),
prod_pack_size VARCHAR2(30 byte),
supplier_id NUMBER(6),
prod_status VARCHAR2(20 byte) NOT
NULL,
prod_list_price NUMBER(8, 2) NOT NULL,
prod_min_price NUMBER(8, 2) NOT NULL,
product_total VARCHAR2(13 byte)
);

CREATE DIMENSION products
LEVEL product IS (products.prod_id, products.prod_desc, products.prod_list_price, products.prod_min_price, products.prod_name, products.prod_pack_size, products.prod_status, products.supplier_id, products.prod_unit_of_measure, products.prod_weight_class)
LEVEL subcategory IS (products.prod_subcategory, products.prod_subcat_desc)
LEVEL category IS (products.prod_subcategory, products.proc_subcat_desc)
HIERARCHY prod_hier (product CHILD OF subcategory CHILD OF category)
ATTRIBUTE product DETERMINES (products.prod_category, products.prod_id)
ATTRIBUTE subcategory DETERMINES (products.prod_category, products.prod_subcategory)
ATTRIBUTE category DETERMINES products.prod_category;

but you'd now also call some additional PL/SQL packages to create Oracle OLAP metadata (known as 'CWM Lite' metadata), to enable access via the Java OLAP API, like this:

begin
cwm_classify.remove_entity_descriptor_use(28, cwm_utility.DIMENSION_TYPE, 'SH', 'PRODUCTS');
cwm_olap_dimension.set_plural_name('SH', 'PRODUCTS', 'PRODUCTS');
cwm_olap_dimension.set_default_display_hierarchy('SH', 'PRODUCTS', 'PROD_HIER');
cwm_olap_dimension.set_display_name('SH', 'PRODUCTS', 'PRODUCTS');
cwm_olap_level.set_display_name('SH', 'PRODUCTS', 'PRODUCT', 'PRODUCT');
cwm_olap_level_attribute.set_name('SH', 'PRODUCTS', 'PRODUCT', 'PROD_CATEGORY', 'PROD_CATEGORY');
cwm_olap_level_attribute.set_name('SH', 'PRODUCTS', 'PRODUCT', 'PROD_ID', 'PROD_ID');
cwm_olap_level.set_display_name('SH', 'PRODUCTS', 'SUBCATEGORY', 'SUBCATEGORY');
cwm_olap_level_attribute.set_name('SH', 'PRODUCTS', 'SUBCATEGORY', 'PROD_CATEGORY', 'PROD_CATEGORY');
cwm_olap_level_attribute.set_name('SH', 'PRODUCTS', 'SUBCATEGORY', 'PROD_SUBCATEGORY', 'PROD_SUBCATEGORY');
cwm_olap_level.set_display_name('SH', 'PRODUCTS', 'CATEGORY', 'CATEGORY');
cwm_olap_level_attribute.set_name('SH', 'PRODUCTS', 'CATEGORY', 'PROD_CATEGORY', 'PROD_CATEGORY');
cwm_olap_hierarchy.set_display_name('SH', 'PRODUCTS', 'PROD_HIER', 'PROD_HIER');
cwm_olap_dim_attribute.create_dimension_attribute('SH', 'PRODUCTS', 'Long_Description', 'Long_Description', '');
cwm_classify.add_entity_descriptor_use(41, cwm_utility.DIMENSION_ATTRIBUTE_TYPE, 'SH', 'PRODUCTS', 'Long_Description');
cwm_olap_dim_attribute.create_dimension_attribute('SH', 'PRODUCTS', 'Short_Description', 'Short_Description', '');
cwm_classify.add_entity_descriptor_use(42, cwm_utility.DIMENSION_ATTRIBUTE_TYPE, 'SH', 'PRODUCTS', 'Short_Description');
cwm_olap_dim_attribute.add_level_attribute('SH', 'PRODUCTS', 'Long_Description', 'PRODUCT', 'PROD_ID');
cwm_olap_dim_attribute.add_level_attribute('SH', 'PRODUCTS', 'Short_Description', 'PRODUCT', 'PROD_CATEGORY');
cwm_olap_dim_attribute.add_level_attribute('SH', 'PRODUCTS', 'Long_Description', 'SUBCATEGORY', 'PROD_SUBCATEGORY');
cwm_olap_dim_attribute.add_level_attribute('SH', 'PRODUCTS', 'Short_Description', 'SUBCATEGORY', 'PROD_CATEGORY');
cwm_olap_dim_attribute.add_level_attribute('SH', 'PRODUCTS', 'Long_Description', 'CATEGORY', 'PROD_CATEGORY');
cwm_olap_dim_attribute.add_level_attribute('SH', 'PRODUCTS', 'Short_Description', 'CATEGORY', 'PROD_CATEGORY');
cwm_classify.add_entity_descriptor_use(41, cwm_utility.LEVEL_ATTRIBUTE_TYPE, 'SH', 'PRODUCTS', 'CATEGORY', 'PROD_CATEGORY');
cwm_classify.add_entity_descriptor_use(41, cwm_utility.LEVEL_ATTRIBUTE_TYPE, 'SH', 'PRODUCTS', 'SUBCATEGORY', 'PROD_SUBCATEGORY');
cwm_classify.add_entity_descriptor_use(41, cwm_utility.LEVEL_ATTRIBUTE_TYPE, 'SH', 'PRODUCTS', 'PRODUCT', 'PROD_ID');
cwm_classify.add_entity_descriptor_use(42, cwm_utility.LEVEL_ATTRIBUTE_TYPE, 'SH', 'PRODUCTS', 'PRODUCT', 'PROD_CATEGORY');
cwm_classify.add_entity_descriptor_use(42, cwm_utility.LEVEL_ATTRIBUTE_TYPE, 'SH', 'PRODUCTS', 'SUBCATEGORY', 'PROD_CATEGORY');
cwm_classify.add_entity_descriptor_use(42, cwm_utility.LEVEL_ATTRIBUTE_TYPE, 'SH', 'PRODUCTS', 'CATEGORY', 'PROD_CATEGORY');
commit;
end;

All of this additional work is carried out for you automatically, when you use the Oracle Enterprise Manager GUI to create your dimension, or you can enter the commands manually, as listed above.

With the Oracle 9i OLAP Option, as well as dimension objects, you can also create cube objects. Cube objects are one or more measures, that are dimensioned by by a common set of dimension objects. Cubes are then used by the Java OLAP API, and tools that use the API such as Oracle Business Intelligence Beans, and Discoverer 10.1.2 'Drake', as the basic building blocks of OLAP reports.

To create a simple cube that has one measure and uses our one dimension, first of all create a table to contain the measure

CREATE TABLE sales_measure (
prod_id NUMBER(10) NOT NULL,
amount_sold NUMBER(10) NOT NULL
);

Then run some additional PL/SQL to create our OLAP API objects.

begin cwm_utility.set_object_in_error(null, null, null, null);
end;
ALTER TABLE SH.SALES_MEASURE
ADD CONSTRAINT FK_ON_0PRODUCTS_PROD_ID_SALES_ FOREIGN KEY(PROD_ID)
REFERENCES SH.PRODUCTS(PROD_ID, PROD_DESC, PROD_LIST_PRICE, PROD_MIN_PRICE, PROD_NAME, PROD_PACK_SIZE, PROD_STATUS, SUPPLIER_ID, PROD_UNIT_OF_MEASURE, PROD_WEIGHT_CLASS) RELY DISABLE NOVALIDATE
declare PRODUCTS number;
tmp number;
begin
CWM_OLAP_CUBE.Create_Cube('SH', 'SALES', 'SALES', '');
PRODUCTS := CWM_OLAP_CUBE.Add_Dimension('SH', 'SALES', 'SH', 'PRODUCTS', 'PRODUCTS');
CWM_OLAP_CUBE.Set_Default_Calc_Hierarchy('SH', 'SALES', 'PROD_HIER', 'SH', 'PRODUCTS', 'PRODUCTS');
CWM_OLAP_CUBE.Map_Cube('SH', 'SALES', 'SH', 'SALES_MEASURE', 'FK_ON_0PRODUCTS_PROD_ID_SALES_', 'PRODUCT', 'SH', 'PRODUCTS', 'PRODUCTS');
CWM_OLAP_MEASURE.Create_Measure('SH', 'SALES', 'AMOUNT_SOLD', 'AMOUNT SOLD', '');
CWM_OLAP_MEASURE.Set_Column_Map('SH', 'SALES', 'AMOUNT_SOLD', 'SH', 'SALES_MEASURE', 'AMOUNT_SOLD');
tmp:= cwm_utility.create_function_usage('SUM');
cwm_olap_measure.set_default_aggregation_method('SH', 'SALES', 'AMOUNT_SOLD', tmp, 'SH', 'PRODUCTS', 'PRODUCTS');
commit;
end;

Now, we've created dimensions and cubes using relational tables, and the Oracle OLAP Option, and we can then go on to analyse these using OLAP API-aware tools such as BI Beans, Discoverer 'Drake' and the Excel Add-in.

As an alternative to storing dimensions and measures in relational tables, we can also store them in analytic workspaces. Analytic Workspaces are multidimensional workspaces held within LOBs in Oracle tables, that store data using a technology originally introduced with Oracle's Express line of products. Oracle Express was originally a product designed and sold by a company called IRI, who sold the technology to Oracle in 1995 who then rebadged it and sold it as a specialist OLAP server product for high-end analysis. Eventually, Oracle took this technology and incorporated it into Oracle 9i, and you can now store your OLAP data in these Express-derived analytic workspaces if your application requires high-end analysis, forecasting, analysis or OLAP calculations. Because the OLAP Option is based on the Express Server calculation engine and multidimensional datatypes, it brings across all the Express functionality such as forecasts and demand plans, support for financial models, allocations and budgeting, and support for what-if analysis. Also, unlike relational OLAP cubes, multidimensional OLAP Option cubes are usually “fully solved,” with all aggregations computed at load time, giving a faster, more predictable response time for users’ queries.

The first step in working with multidimensional datatypes is to create an analytic workspace.

To create your first analytic workspace, start up Analytic Workspace Manager (a standalone download from OTN), log in as your schema owner, then select Tools > OLAP Worksheet from the menu bar. Think of the OLAP Worksheet as the equivalent to the SQL Worksheet; commands you type are in the bottom pane, with the output displayed in the top pane.

The OLAP Worksheet uses a special language for Oracle OLAP, called OLAP DML. Based on the Express SPL, OLAP DML is a language not unlike PL/SQL that’s been around for over 20 years, and allows you to create, query, and programmatically control multidimensional datatypes using procedural constructs such as conditions, loops, and subroutines. You can enter OLAP DML commands using the OLAP Worksheet, or you can execute them from PL/SQL using the DBMS_AW.EXECUTE procedure. All the while you’re in the OLAP Worksheet you’re actually working within an Oracle schema, and in fact you can switch between OLAP DML and SQL if you need to execute an SQL command.

To create your first analytic workspace, type in

aw create my_first_aw

Your analytic workspace should now be created. If you take a look in your schema, you’ll find a new table with an AW$ prefix, that contains the analytic workspace you’ve just created.

Next, we need to create some dimensions. In our case we want to create two dimensions, a new one called “Geography” and one called “Product.”, like the one we created relationally beforehand. Note however that these two product dimensions are completely separate and have no relation between each other.

Unlike the Oracle CREATE DIMENSION statement that defines all the dimension levels and the hierarchies in one go, with OLAP DML, you define all the levels as individual dimensions, wrap this up in a “concat” dimension that concatenates the values in individual dimensions, then create what’s called a “relation” object that describes the hierarchical relationship between the level values. For example, with our Geography dimension, we’d type in

CREATE city DIMENSION TEXT
CREATE county DIMENSION TEXT
CREATE country DIMENSION TEXT

DEFINE geography DIMENSION CONCAT (country county city)

DEFINE geography.parents RELATION geography <geography>

The key differences between OLAP Option dimensions and relational dimensions are that relational dimensions use level-based dimensions, whilst OLAP Option dimensions are parent-child based. Level-based Dimensions’ hierarchies are defined by the relationship between levels, and levels map to columns in relational tables. Oracle OLAP dimensions, however, use parent/child relationships between levels, where dimension members map to a parent column and a child column. The parent/child combination in a given row expresses a hierarchical relationship, and this relationship is stored in the analytic workspace relation object. The advantage of the parent/child approach is that unbalanced or ragged hierarchies can be more easily used, as each route down the hierarchy doesn’t need to contain the same number of levels and can be individually defined for each dimension member.

The values that are to be contained in the dimensions are loaded in a later process, together with the links between dimension members that are loaded into the relation object.

Now, do the same for our products dimension

CREATE sku DIMENSION TEXT
CREATE category DIMENSION TEXT
CREATE class DIMENSION TEXT

DEFINE products DIMENSION CONCAT (class categoy sku)

DEFINE products.parents RELATION products <products>

Once we have created our dimension objects, we next create a variable to hold our transactional data. A variable is like a fact table with one fact column, and is defined thus:

DEFINE sales VARIABLE NUMBER (10,2) < geography products>

This tells the OLAP Option to create a variable called “sales,” and dimension it by our geography and products concatenated dimensions.

What we've done so far with our analytic workspace is create some dimensions, and a variable to hold some sales data. One problem we've got, however, is that none of this is so far visible to the Java OLAP API, and to make it visible, we've got to create some additional metadata within the analytic workspace.

With Oracle 9i, is a bit of a complicated task, as it has to be done manually, and consists of a two-step process.  

  1. Create relational views of the data. These views take the place of fact tables and dimension tables where they do not exist (in the case of multidimensional data).
  2. Use the OLAP Catalog CWM2 PL/SQL procedures to generate the metadata.

Creating this metadata is out of scope of this article, but for more details, take a look at the white paper "Migrating Express Applications To Oracle 9i"

Happily however, this entire process is made considerably easier with Oracle 10g. Like Oracle 9i, Oracle 10g includes an OLAP Option, but with Oracle 10g any objects created in analytic workspaces are automatically enabled for Java OLAP API analysis, so there's no manual process to go through.

So what we've shown here is several things. Firstly, with Oracle 8i, you could create basic relational OLAP dimensions, which were used by the query rewrite and materialized view mechanisms to make summary management more effective. With Oracle 9i, you can still create these dimensions, but if you use the OLAP Option , you can also create 'enhanced' relational OLAP dimensions, and now also cubes, that work with the Java OLAP API. In addition, you can create your dimensions and cubes within analytic workspaces, if you want to take advantage of the advanced OLAP features of the Oracle OLAP/Express engine.

Going back then to the original question, first of all, if you're creating relational OLAP dimensions and cubes, you don't need to create additional tables to hold your data, as your dimensions and cubes are just additional metadata that sits on top of existing tables that is later used by either the query rewrite mechanism, the summary advisor, or by OLAP tools that use the Java OLAP API. If, however, you're creating multidimensional OLAP dimensions and cubes, and storing them in analytic workspaces, you'll need to create dimension and variable objects within the analytic workspace, and you'll either need to manually enable them for the OLAP API if you're using Oracle 9i, or they'll be automatically enabled for you, if you're using Oracle 10g.

For more details on the various OLAP implementations with Oracle 9i and 10g, take a look at these further articles;

相关文章推荐

Understanding Instance and Class Members(Reprint from http://download.oracle.com/javase/tutorial/ja)

Understanding Instance and Class MembersIn this section, we discuss the use of the static keyword to...

How do I choose grid and block dimensions for CUDA kernels?

The answers above point out how the block size can impact performance and suggest a common heuristic...

Codeforces Round #297 (Div. 2) E题. Anya and Cubes (中途相遇法)

题目地址:Anya and Cubes 比赛的时候居然没想起中途相遇法。。。这题也是属于想起来就很简单系列。 中途相遇法也叫折半搜索。就是处理前一半,把结果储存起来,再处理后一半,然后匹配前一半存...

Codeforces Round #297 (Div. 2)E. Anya and Cubes 折半搜索

题目连接:http://codeforces.com/contest/525/problem/E题意:n个数,k个魔法棒,s为所求的数,然后让你找有多少种方法,能够使的这n个数之和为s,其中一个魔法棒...

codeforces 525 E Anya and Cubes 中途相遇法

codeforces 525 E Anya and Cubes 中途相遇法 题意: 给出n个数a1,a2,...,an,要求从中选出一些数,可以把其中最多k个变成它自己的阶乘,然后选出的数求和,问...

#297 (div.2) E. Anya and Cubes

1.题目描述:点击打开链接 2.解题思路:本题利用双向查找解决。双向查找一般用于求若干个数之和相加等于一个固定值的题目。一般方法是将n个数分为两部分:1~n/2和n/2+1到n,然后枚举出两部分的所...

Codeforces Round #297 (Div. 2)---E. Anya and Cubes

Anya loves to fold and stick. Today she decided to do just that.Anya has n cubes lying in a line and...

Codeforces Round #297 (Div. 2) E. Anya and Cubes

中途相遇法。。分成两半计算。。。 #include #include #include #include #include #include #include #include #i...

Codeforces Round #395(Div. 2)B. Timofey and cubes【模拟】

B. Timofey and cubes time limit per test 1 second memory limit per test 256 megabytes input stand...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)