7 Replies Latest reply: May 8, 2013 6:17 PM by 684553 RSS

    Create OLAP Dimensions from Multiple Relational Tables

      I am using Oracle 11g R2 and trying to create an Oracle OLAP Dimension [using OLAP DML Statements]. I am able to create a dimension with all the levels and attributes define in one single table as source for this dimension, but if my source is a snowflake and has more than on table for a single dimension, I am not able to define joins properly for all the levels.
      eg: "Customer table" has all customer related attributes but it's level are city,zip,state,country which comes from another table "Customer Address". this is just an example. So, can someone provide me a complete working OLAP DML Statement using joins in this scenario?

      I tried with AWS but I might be missing something and couldn't create it.
      The Oracle documentation doesn't talk much about joining source tables in dimension statements "Create Dimension" or in AWS documents

      any help is greatly appreciated.

        • 1. Re: Create OLAP Dimensions from Multiple Relational Tables
          Why are you creating dimensions using OLAP DML instead of AWM (Analytic Workspace Manager)? What is AWS?
          • 2. Re: Create OLAP Dimensions from Multiple Relational Tables
            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
            • 3. Re: Create OLAP Dimensions from Multiple Relational Tables
              Is there any error on screen?

              In the mapping, did you select "Snow Flake Schema" option? The default option is "Star Schema".
              • 4. Re: Create OLAP Dimensions from Multiple Relational Tables
                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]
                • 5. Re: Create OLAP Dimensions from Multiple Relational Tables
                  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.
                  • 6. Re: Create OLAP Dimensions from Multiple Relational Tables
                    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]
                    • 7. Re: Create OLAP Dimensions from Multiple Relational Tables
                      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.