Database Software

Why are you creating dimensions using OLAP DML instead of AWM (Analytic Workspace Manager)? What is AWS?

Thanks for your response.
Sorry! I meant AWM (Analytical Workspace Manager). Why I'm trying to manualy create dimensions instead of AWM?.. to get hands on and create Dimensions Manually. But like I mentioned earlier, even with AWM, I am not able to get data by joining tables [I am able to create Dimensions].

Any pointers would be helpful.

Edited by: Maverick439 on May 8, 2013 9:04 AM

Is there any error on screen?

In the mapping, did you select "Snow Flake Schema" option? The default option is "Star Schema".

Well, I am trying to find where is an option to give join condition while creating dimension. I don't have referential integerity [foreign keys] on these tables. Will that be neded for AWM to figure out join automaticaly or can I mention it somewhere?
Also, I'm mostly interested in DML options rather using AWM. So, I'm still interested in OLAP DML [create dimension manually]

DEFINE dimension command documentation is at: http://docs.oracle.com/cd/E11882_01/olap.112/e17122/dml_commands_1032.htm#i78498

But if you create the dimension using olap dml command, then
(1). it will not be visible in AWM
(2). you will have to write your own olap dml program to load it

What is the reason for creating dimension manually using OLAP DML language?

Generally we don't do have to do this these days.

Almost all logic is "pushed" to sql views, which are then "mapped" to olap dimensions and attributes.
Its much simpler and easier to write logic in sql-view for each attribute and each level.

So you can create sql-view (for each hierarchy) and ensure all referential integrity in that sql-view and then "map" that sql-view to your dimension.

I prefer creating a sql-view with all required columns in it, and then in dimension mapping I always select "Star Schema" option.

Thanks again for the immediate repsonse. We don't use Oracle OBIEE for our Business Intelligence system [only Oracle Database] So we are not sure if this other tool can understand Cubes built using AWM tool. So, we thought of manually creating cubes and materialized Views [with Query rewrite option] which can be used by third party BI Tools [like SAP BO or Cognos]

sql-views option is good technique for AWM but I don't think "Create dimension" statements will accept sql-views or view objects . They need table objects directly as source to create a dimension [I think]

I can understand circumstances for not going full blown Oracle metadata. Doing all your definitions in dml can give you an awful lot of flexibility and access to powerful things which can be difficult with objects created with READ ONLY and LOCKDFN... Having said that, if your queries are dealing with requirements solved while working within the framework - performance can literally be orders of magnitude better. Much of the restrictions would be due to integration with the Oracle data dictionary, hence some restrictions.

As far as complex view creation within the create dimension - I would side with Nasar - usually I like to do more complex relational joins within the relational side. I am often not a fan of imbedding too complex of a construct within another language (ergo sql within java, olapdml etc etc.) but to either make a view, or in other cases that require more complex processing to make a pl/sql procedure to do that. If you are doing the complex stuff in OLAP with OLAP objects- then DML is great. Specifically for analytics.

Karl