6 Replies Latest reply: Dec 29, 2012 5:44 AM by Juriaan van Oosterom RSS

    Olap type in data store properties

    970126
      Hello,

      Can you please tell me what is the purpose of defining the OLAP type (dimension, fact table, SCD) in the data store properties in ODI? I'm already familiar with dimensions & fact tables as used in data warehousing modeling. I am just wondering how specifying the OLAP type of a data store affects its behavior.

      Thank you,

      Anju
        • 1. Re: Olap type in data store properties
          PeakIndicators_Alastair
          Hi,
          Pretty sure it opens up the UI to set more options depending on that option, not at PC so cant check.
          e.g SCD - Surrogate Key, Current Record, Start Date, End Date etc.
          • 2. Re: Olap type in data store properties
            970126
            thanks for your response.
            I'm using Oracle data integrator 10.1.3, and i cannot see any other UI for defining surrogate keys etc. can you please advise where i can see these properties?

            thanks
            • 3. Re: Olap type in data store properties
              JeromeFr
              Hello,

              You can define SCD2 behavior here after enabling OLAP type Slow Changing Dimension :

              Designer > Model > Your Model Folder > Your Model > Your datastore > Columns and double click on YOUR_COLUMN. Select the Description tab, set Slowly
              Changing Dimension Behavior to Surrogate Key or anything else you want.


              Hope it helps.


              Kind regards,
              JeromeFr
              • 4. Re: Olap type in data store properties
                970126
                Thanks Jerome.
                I can see that i have the possibility of selecting other attributes as well in the description tab (natural key, surrogate key, start time, end time etc...). Are these just for defining the columns or do they have other properties as well? Can you please guide me to any documentation that provides more details (if any).

                thanks
                Anju
                • 5. Re: Olap type in data store properties
                  JeromeFr
                  If you set one of these properties on each column, then you can use a KM like this one : IKM Oracle Slowly Changing Dimension and it will implement SCD by itself.

                  For example if you have this source table and a target table with the same structure + two columns for the date (start and end) :

                  Source table PRODUCT :
                  ID .... BID ... NAME ..... PRICE
                  1 ..... 10 ..... ODI ....... 10

                  You set your properties onto your target datastore : ID as a surrogate key, BID as natural key, NAME as Overwrite on Change, PRICE as Add Row On Change, START as Starting Timestamp and finally END as Ending Timestamp.

                  You execute the interface a first time. You target datastore will contain something like this :
                  ID .... BID ... NAME ..... PRICE .... START ........... END
                  1 ..... 10 .... ODI ........ 10 ......... 30/10/2012 ... 31/12/3000

                  The next day, change the name of the product to OWB in your source and execute the interface again. Your target will look like this :
                  ID .... BID ... NAME ..... PRICE .... START ........... END
                  1 ..... 10 .... OWB ........ 10 ......... 30/10/2012 ... 31/12/3000


                  The next day, change the price of the product to set it to 20. Your target will look like this :
                  ID .... BID ... NAME ..... PRICE .... START ................. END
                  1 ..... 10 .... OWB ........ 10 ......... 30/10/2012 ........ *31/10/2012*
                  *1 ..... 10 .... OWB ........ 20 ......... 01/11/2012 ...... 31/12/3000*


                  This should help you but I'll try to look after some documentation. If I don't find it I'll write an article by myself next week ;).


                  Hope it helps.

                  Regards,
                  Jerome
                  • 6. Re: Olap type in data store properties
                    Juriaan van Oosterom
                    Hallo,
                    The row behaviour options are available in the ui even if the olap_type is not set on the datastore.
                    What is the purpose of defining the olap propperty on Datastore level??
                    I can see in the documents the olap type propperty is used by some KM.
                    But; what happens to the proces?
                    (The method description in the substitution api is not clear on this).
                    Should i specify this?
                    What are the side effects of leaving it to the default value of 'Unknown'.?