• RELEVANCY SCORE 3.83

    DB:3.83:Create Key For Dimension Using 2 Columns 1k




    Hello,
    I have table which unique id are two columns. For example:
    A B
    -1 1
    -1 -1
    2 -1

    In the fact table these columns also appear. I would like to ask if it is possible to create dimension in SSAS using these two columns or I have to create real unique one column.

    DB:3.83:Create Key For Dimension Using 2 Columns 1k

    Yes but i would suggest to create physical primary key by including both column if possible otherwise you can also do by logical in DSV .Mukesh

  • RELEVANCY SCORE 3.44

    DB:3.44:Fact Dimension kp




    I have created a Fact Dimension on a Fact Table that has around 4 million rows.  The key for the dimension use 6 different columns.  Does using so many columns in the Key create a performance problem?  Are the any disadvantages in general for using Fact Dimensions insead of a regular dimension?
     
    Thanks,
    Eric Nelson

  • RELEVANCY SCORE 3.38

    DB:3.38:Create Dimension With Cwm2 s1




    Hello,

    With cwm2, can I map a dimension on a table which have 2 key ?
    I do it with OEM : I select 2 column key for on level.

    Thanks,
    Yann

    DB:3.38:Create Dimension With Cwm2 s1

    sure you can:

    looking at the following docs: http://download-west.oracle.com/docs/cd/B10501_01/olap.920/a95295/table_m8.htm#1010504 reveals:

    quote
    Column in the dimension table to which this level should be mapped. This column will be the key for this level column in the fact table.

    If the level is stored in more than one column, separate the column names with commas. These columns will be the multicolumn key for these level columns in the fact table.
    /quote

    regards,
    thomas

  • RELEVANCY SCORE 3.27

    DB:3.27:Slowly Changing Dimension - Type 2 cj


    Hi,

    I'm trying to implement SCD type 2 using the Oracle white paper on the subject but I'm having a problem with the final mapping stage to the dimension table.
    I've followed the various threads on the subject and have used the workaround to create the dimension table in the mapping as follows :-

    1) Add an unbound table, with a name different from the target dimension
    2) Change the unbound table "bound name" to the name of the target dimension
    3) Add, manually, the columns equal to the target dimension
    4) Be prepared to accept warnings for both a non-bound repository object and different column count.
    -- From here, things are described properly in the manual
    5) Add another column for matching etc...

    My problem comes with adding the MATCHING column. I cannot change the Bound Name to that of the surrogate key column as stated in the White Paper. I get the error "API0408 : Mapping Parameter object with name PR_PROJECT_KEY already exits". (PR_PROJECT_KEY is the surrogate key column)

    Anyone any ideas?

    I'm using Warehousebuilder version 9.0.3
    Is this the problem?

    regards

    David Aitken

    DB:3.27:Slowly Changing Dimension - Type 2 cj

    Looks like in your dataset there are two or more records not unique according to the UK of the target table, resulting in this constraint violation.
    You could try a DEDUP, or change loading type to UPDATE/INSERT...

  • RELEVANCY SCORE 3.19

    DB:3.19:Re: Ora-01408: Such Column List Already Indexed pz


    That indicates there is already an index/primary key for the columns (ORGANISATIONID, USERNAME) on that table.
    You either need to drop that other index/primary key first or create your new one using a different combination of columns.

    DB:3.19:Re: Ora-01408: Such Column List Already Indexed pz

    HI when am trying to add constraint in my table am geting this error ORA-01408: such column list already indexed

    Edited by: adf009 on 2013/02/28 1:03 PM

  • RELEVANCY SCORE 3.16

    DB:3.16:Constraint Problem In Foreign Key --- Very Urgent - Help Needed cx


    Hello All,
    There are 2 tables and their associated fields

    EmpProj
    -------------
    Emp_id(pk)
    Proj_id (pk)
    eff_from_dt(pk)

    ProjDesc
    --------------
    Proj_id(fk)
    eff_from_dt(fk)
    Proj_name

    I have created the 2 tables like shown below

    CREATE TABLE EMPPROJ
    (EMP_ID NUMBER,
    PROJ_ID NUMBER,
    eff_from_dte date,
    PRIMARY KEY(EMP_ID,PROJ_ID,EFF_FROM_DT)
    );

    CREATE TABLE PROJDESC
    (PROJ_ID NUMBER,
    PROJ_NAME VRACHAR2(20),
    EFF_FROM_DT DATE,
    CONSTRAINT S2 FOREIGN KEY(PROJ_ID,EFF_FROM_DT) REFERENCES EMPPROJ(PROJ_ID,EFF_FROM_DT));
    );

    Now whenever i try to create a foriegn key table it gives an error message like "No matching parent key found."

    The columns in the foreign key should be same in number, same datatype and size. you can't create a foreign key with one column for a primary key of two columns.

    What i need to do to refer only the two columns in the Primary Key by a Foreign key?

    Please suggest anyway to resolve this problem.

    Thanks in advance.

    Captain

    DB:3.16:Constraint Problem In Foreign Key --- Very Urgent - Help Needed cx

    I wana ask why you are against the rule of RDBMS,any valid reason??

    Khurram

  • RELEVANCY SCORE 3.14

    DB:3.14:Regarding Dimension Creation fc


    Hi,

    When I am creating a dimension it gives the default attributes like

    ID-- surrogate Identifier
    Name-- Business Identifier
    Description Business Identifier

    I have to create a dimension.
    My dimension does not have these attributes.(ID,Name,Description).

    Do I need to take all three default attributes or can I delete it.

    I have to create 2 levels.

    Is it necessary to take surrogate,Business Identifiers for each level.

    Is it compulsary to add Surrogate Identifier for each level.

    How can we decide the surrogate, Business Identifiers.

    My dimension has 3 columns as a Primaykey.
    It is urgent.

    Please give me the suggestions.

    Thanks,
    Venkat

    DB:3.14:Regarding Dimension Creation fc

    Hi Venkat

    You do not need to define surrogate attributes. The slowly changing support in OWB needs surrogates, BUT you can model dimensions specifying just the business attributes (the level identifying attributes) and load the dimension table using a table operator in a map. Now I might be mixed up with all of my OWB versions but the wizard should allow you to delete/rename these attributes.

    Cheers
    David

  • RELEVANCY SCORE 3.13

    DB:3.13:Materialized View - Does Not Contain A Primary Key Constraint 18


    I am trying to create materialized view. I gone through the MV wizard creation. Added 2 columns (foo_column, foo_pk) of the table and have a simple select statement (Select foo_column from foo_dim). Also created a primary key contrainst and refencing the primary key (FOO_PK) of the dimension.

    I am getting the following error:
    ORA-12014: table 'FOO_DIM' does not contain a primary key constraint

    DB:3.13:Materialized View - Does Not Contain A Primary Key Constraint 18

    It was solved. The table that I am querying has to have a primary key defined before creating a materialized view.

  • RELEVANCY SCORE 3.11

    DB:3.11:Info Regarding Fact Dimension Table Mapping xd



    Hi,Hope doing fine, as such If you dont mind can you kindly get me some clear info regarding the mapping of dimension fact tableHere is my approach do let me know whether Im right or wrong in this approach(1) First I have built my targets (dimension Fact Tables) by creating cube and then I created 2 separate mappings one for Dimension and the other for Fact table (2) Under the dimension mapping I placed a sequence Trans for each target to map it with my target tables surrogate keys(3) In the Fact mapping I took a single source Qualifier reading all the sources at once and mapped my facts (measures) in the fact table to the columns from the source qualifier and then placed a lookup (which is taken form targets) for each foreign key in the fact table to mapped it with the lookup table surrogate key column.And then finally scheduled my session in the sequence so that first dimension gets updated and then fact table Here I also have one query regarding dropping and re creating of indexes in my target tables is there any other way apart form writing a Pre Post SQL to drop and re create the Indexes on my target table. Because leaving my target table with the default index is making my session fail with a Database driver error...I would once again appreciate for your time.Thanks RegardsRajender

    DB:3.11:Info Regarding Fact Dimension Table Mapping xd


    Hi,Hope doing fine, as such If you dont mind can you kindly get me some clear info regarding the mapping of dimension fact tableHere is my approach do let me know whether Im right or wrong in this approach(1) First I have built my targets (dimension Fact Tables) by creating cube and then I created 2 separate mappings one for Dimension and the other for Fact table (2) Under the dimension mapping I placed a sequence Trans for each target to map it with my target tables surrogate keys(3) In the Fact mapping I took a single source Qualifier reading all the sources at once and mapped my facts (measures) in the fact table to the columns from the source qualifier and then placed a lookup (which is taken form targets) for each foreign key in the fact table to mapped it with the lookup table surrogate key column.And then finally scheduled my session in the sequence so that first dimension gets updated and then fact table Here I also have one query regarding dropping and re creating of indexes in my target tables is there any other way apart form writing a Pre Post SQL to drop and re create the Indexes on my target table. Because leaving my target table with the default index is making my session fail with a Database driver error...I would once again appreciate for your time.Thanks RegardsRajender

  • RELEVANCY SCORE 3.10

    DB:3.10:Named Calculation In Dsvs Fact Table Vs Reference Dimension Relationship mf


    I have two dimension tables in my Data Source View with the following columns:
    DimGroup:
    Group Key
    Group
    DimPerson:
    Person Key
    Group Key
    Name
    Country
    I also have a fact table with the following columns:
    FactPoints:
    Person Key
    Group Key*
    Points
    *: The Group Key column of the fact table is really just a named calculation that uses the Person Key to figure out what the Group Key will be. Therefore, although the underlying database's fact table only has two columns, my cube's measure group (the one
    that contains Points) is linked to two dimensions.
    My question is the following:
    Is this the sort of situation where I should be using a reference dimension relationship ([1],

    [2],
    [3]) instead? Am I doing the same thing in a more hacky way? What are the similarities and differences between the two approaches for this problem? How does performance factor into this?

    DB:3.10:Named Calculation In Dsvs Fact Table Vs Reference Dimension Relationship mf

    Unless there is a valid need - such as a accessing a generic dimension such as DimDate with rich attributes which you don't want to replicate across many dimensions.
    Refer thispage, to know about the advantages
    / problems with Reference Dimensions.
    Reference Dimensions are costly, in terms of processing performance / query performance (depends whether you have Materialize option checked or not).

    Refer this
    page, to know more about Materialize option.
    In your example, if you want the Dimension DimGroup to Access FactPoints via DimPerson, that would be Reference dimension. If DimGroup has many attributes, which you want to share between different dimensions, you may want to use Reference dimension.
    I think your approach is better since you have avoided the Reference dimension and there is no query time impact.
    Jegan - SQL Server BI Consultant (http://EasyBI.wordpress.com)

  • RELEVANCY SCORE 3.07

    DB:3.07:Extra Dimension Or Add To Fact Cube Dimension c1


    I'm facing this situation several times when I'm designing the table structure of a Data Warehouse: Let's say I have a fact table with sales transactions. I want to add a dimension that separates the sales transactions into direct and indirect sales. I'd
    have two options for this:

    Create a dimension table called dimSalesType that contains two columns. An integer column for the PK and a description for the type of sales. In the fact table, I add a column with a foreign key to the dimension table. I'd create a new dimension
    in the SSAS project and link it in the cube. Create a column in the fact table called SalesType, and fill the values either with the text Direct or Indirect. I'd create a dimension from the fact table and link the dimension to the cube as a fact type relationship.

    For option 2 I'd imagine the fact table would be considerably bigger in size because I'd store text instead of integers in that column. The thing is, when I choose option 1, I could end up in a lot of dimensions with very small dimension tables.
    Which one would be preferrable and why?
    Thanks!-

    DB:3.07:Extra Dimension Or Add To Fact Cube Dimension c1

    Hi Erik,
    I'd like Option 2. There is no need to add complextity to the data warehouse to represent just one attributes as a separate dimension even thoughduplicate the data in the fact tables. And opt 2 can have a better performance than opt 1 in the queriesagainst
    this cube. Yes, you need create a fact dimension based on the fact table and link the dimension to the cube as a fact type relationship.
    Regards,
    Jerry

  • RELEVANCY SCORE 3.07

    DB:3.07:Datawarehouse: Design For Effectivity 7z


    Hi
    Has anyone designed a warehouse , whose dimension sources contain effectivity.

    An example.
    I would like to create a member dimension.
    Source:
    Table : Member
    Columns: member_Seqnum(pk),member_id (ak), first_name, last_name

    Table : Policy
    Columns: Policy_Seqnum (Pk) ,policy_id, eff_start, eff_end.

    Member
    Seqnum member_id first name last_name
    1 member1 first1 last1
    2 member2 firstr2 last2

    Policy
    Seqnum Policy Id Eff_start eff_end
    1 Policy1 01/01/2007 12/31/2007
    2 Poilcy1 01/01/2008 12/31/2008

    I'm looking for help in mapping the columns to the dimension, do the effective columns come over as attributes in the dimension, or do I spawn a new dimension for the effectivity hence a snowflake model.

    Also how have you handled this through the etl process

    Any help would be appreciated.

    thanks

    DB:3.07:Datawarehouse: Design For Effectivity 7z

    Hi
    Has anyone designed a warehouse , whose dimension sources contain effectivity.

    An example.
    I would like to create a member dimension.
    Source:
    Table : Member
    Columns: member_Seqnum(pk),member_id (ak), first_name, last_name

    Table : Policy
    Columns: Policy_Seqnum (Pk) ,policy_id, eff_start, eff_end.

    Member
    Seqnum member_id first name last_name
    1 member1 first1 last1
    2 member2 firstr2 last2

    Policy
    Seqnum Policy Id Eff_start eff_end
    1 Policy1 01/01/2007 12/31/2007
    2 Poilcy1 01/01/2008 12/31/2008

    I'm looking for help in mapping the columns to the dimension, do the effective columns come over as attributes in the dimension, or do I spawn a new dimension for the effectivity hence a snowflake model.

    Also how have you handled this through the etl process

    Any help would be appreciated.

    thanks

  • RELEVANCY SCORE 3.07

    DB:3.07:Error While Loading Data In To Fact Table In Ssis Package cx


    Hi,
    In my database we require 5 parameter tables and 2 transaction tables to create cubes.
    Here each table is having composite keys(i.e.2 or 3 primary keys). now i have to create a DWH with 5 dimension tables and 2 fact tables.
    eg:
    ABC table:(PARAMETER TABLE)
    columns:
    W,
    X,(PRIMARY KEY)
    Y,(PRIMARY KEY)
    Z
    and
    DEF Table(Transaction table):
    columns: P(primary key)
    Q(primary key)
    R(primary key)
    S,
    Y

    To get a unique record from the table I need to create composite keys for my dimension tables,If I create with composite keys it should perform more joins and performance will be poor when i create a cube

    so i am loading the unique records in to dimension table with a new column (eg: T column ) and created primary key(i.e surrogate key) with identity column for that table.
    so when I am loading data in to my dimension table it was loaded successfully in ssis,
    when I am loading the transaction table data in to fact table(i.e DWH) I have created the new column T with foreign key and given NOT NULL

    But when I am loading the data to fact table my ssis package is failed with error.
    error:cannot insert NULLS values in to the column
    Can any one help me of what was wrong ?
    Actually I should not get Null values,I should get the dimension table primary key values
    Is this the better way or let me know if any better way to design my DWH and Cubes in ssas with good performance

    Expecting Valuable Suggestions

    Thanks Regards
    Praveen

    DB:3.07:Error While Loading Data In To Fact Table In Ssis Package cx

    Hi Praveen,
    How are you doing the lookup to your dimension table to get the surrogate key? Are you using a lookup component? If you are, how did you configure the error output for the lookup component (Fail component, Ignore failure, Redirect row).
    If SSIS is trying to insert null values into your fact table, your lookup is most probably failing either because:

    your lookup is configured incorrectly, oryou have orphan keys in your fact data, i.e. there are records in your fact data that does not have a corresponding record in the dimension table.
    Try adding a data viewer just after your lookup component and have a look at the data.
    Let me know if you are still having problems.
    Regards,

    Marius (Microsoft BI Solutions Architect)
    Please use Marked as Answer if my post solved your problem and use
    Vote As Helpful if a post was useful.

  • RELEVANCY SCORE 3.05

    DB:3.05:Get Problem When Create Dimension Using Dimension Build Rule File mc


    I got the following warning when I tried to load dimension. I have a product dimension hierarchy which contains 6 levels. I manually create a dimension call product which include six generations. I also create a rule file using SQL. This SQL selects 6 columns and I map each column to each generation. When I load data to build dimension, I always got the following warning and only some part of data get loaded. Anybody know that? Do I have to load parent first before loading child or I can load them at same time?

    Thanks

    \\Record #1 - Incorrect Parent [10] For Member [10] (3307)
    1231010171
    \\Record #2 - Incorrect Parent [1] For Member [1] (3307)
    1118839

    DB:3.05:Get Problem When Create Dimension Using Dimension Build Rule File mc

    My guess is that you are only going to load data at the lowest level (what I called SKU) There are two possibilities
    1. Don't add the prefix to the lowest level(sku) so when you load data it is the same
    2. Modify the data load rule(s) to have the same prefix/sufix as the dimension duild for the level of data you are loading (Again hopefully level 0)

  • RELEVANCY SCORE 3.05

    DB:3.05:How To Implement Dwh Audit Columns In Ikm Oracle Scd, Deleted_Flag ps


    Hello, we are using ODI to implement DataWareHouse project. I need to populate dimensional table using SCD2, but need to carry right values in standard DWH audit columns

    I have slowly changing columns defined on the table
    surrogate key
    natural key
    overwrite on change (SCD1)
    add row on change (SCD2)
    current record flag
    starting ts
    ending ts
    "undefined" is used for standard DWH audit columns

    I customized build-in IKM oracle slowly changing dimensions module to meet our requirements:
    1) fixed SCD values of current flag and future date are now otions
    current flag as new option (Y,N not 1,0)
    future date as new option (1.1.3000 not 1.1.2400)

    2) population of standard DWH audit columns- they ARE marked as "undefined" SCD behavior in models section
    INSERTED_DATE
    UPDATED_DATE
    INSERTED_ETL_ID
    UPDATED_ETL_ID
    DELETED_FLAG

    then by using UD checkboxes (UD1=insert, UD2=update) in interface I populate these columns depending on action (I modified KM steps)

    What I need is to find how to manipulate DELETED_FLAG DWH audit column when populating dimension. I think a new step in KM is needed....
    DELETED flag means that the row was deleted on source, it is not comming now, so we need to mark all rows (matching natural_key) in dimensional table as DELETED_FLAG = Y

    Has anybody developed such KM step?

    Edited by: kubis on 6.5.2010 1:50

    DB:3.05:How To Implement Dwh Audit Columns In Ikm Oracle Scd, Deleted_Flag ps

    hello, I developed a new KM step, called "Set DELETED_FLAG in target". Put it at the end of IKM module (eg. Oracle Incremental Update (MERGE)) before final commit.

    We have full extract in the source - so comparing to target dimension, we can find rows which did not come.

    note:
    I use custom UD3 in interface to mark the DELETED_FLAG column
    I use custom UD2 in interface to mark Audit DHW columns UPDATED_DATE and UPDATED_ETL_ID
    I use custom UD1 in interface to mark Audit DHW columns INSERTED_DATE and INSERTED_ETL_ID

    /*
    oznaci jako smazane zaznamy DELETED_FLAG=YES v TRG tabuli, ktere neprisly ze SRC systemu,
    udelame pomoci megre, kde se provede rozdil TRG-SRC a dany ROZDIL se zupdatuje na sloupce oznacene jako DELETED_FLAG (UD3) a update date a etl_id audit (UD2)
    */
    merge into
    %=odiRef.getTable("L", "TARG_NAME", "A")% TRG
    using(
    select %=odiRef.getColList("", "\tTRG.[COL_NAME]", ",\n\t", "", "UK")%
    from %=odiRef.getTable("L", "TARG_NAME", "A")% TRG

    minus

    select %=odiRef.getColList("", "\t[EXPRESSION]", ",\n\t", "", "UK")%
    from %=odiRef.getFrom()%
    where (1=1)
    %=odiRef.getJoin()%
    %=odiRef.getFilter()%
    %=odiRef.getJrnFilter()%
    %=odiRef.getGrpBy()%
    %=odiRef.getHaving()%
    ) ROZDIL
    on (
    %=odiRef.getColList("", "\tTRG.[COL_NAME] = ROZDIL.[COL_NAME]", "\n\tand\t", "", "UK")%
    )
    when matched then update set
    %=odiRef.getColList("", "TRG.[COL_NAME] = '#c_YES_FLAG'", ",\n\t", "", "UD3")%
    %=odiRef.getColList(",", "TRG.[COL_NAME] = [EXPRESSION]",",\n\t\t\t","","UD2")%

  • RELEVANCY SCORE 3.04

    DB:3.04:Calculate Key Figure Or Formula? x7



    Hi All,

    I am creating a cell based report with structures for row and column.

    There are nearly 200 rows and 35 columns.

    All the columns are using the same key figure, but with different restrictions.

    Now the issue is that, the user wants to see only the absolute values in the report ( ABS() )

    I have two options:

    1) Create a calculated key figure with ABS ( Orig_Key_Figure ). and replace the original key figure in all the columns with the new calculated key figure.

    or

    2) Create 35 extra Formula columns Like ABS ( Column_1 ) , ABS ( Column_2 ) ... ABS ( Column_35 ) etc...

    Now my question is that which one will be better in performance?

    Effort wise, 1st one will be way simpler than the second one. But I am looking for the performance factor...

    Please advise. Reward points are assured...

    Thanks in Advace...

    Regards,

    BIJESH

    DB:3.04:Calculate Key Figure Or Formula? x7


    Thanks to All of you for your kind and valuable opinions.

    Anyways, I proceeded with CKF. Coz, whichever method i use in this query will have to be used in another 7 queries. There are queries with more than 100 columns.

    So, if i went with formula method, it will defenitely take a toll on performance while execution for those huge queries. Not only that, what ever output is generated, that has to be transferred to a COGNOS report, which is being viewed by the end user. So, 100 extra columns will exstensively increase the data volume and will make the overall execution time pretty high.

    Once again, Thank You All.....

    Regards,

    Bijesh

  • RELEVANCY SCORE 3.03

    DB:3.03:Partitioning Using A Dimension Key zf


    Hi -
    We have a dimension called Partner. We want to partition the facts for every partner using the partner dimension key. The challenge we have is that the dimension key is generated by OWB during run time, so we cannot create the partitions in the MDL. Is there any way to accomplish this?

    One workaround is to use the natural key as the surrogate key? Can this be done in OWB?

    DB:3.03:Partitioning Using A Dimension Key zf

    Currently.... to partition the fact table by a meaningful column (for example a DATE column representing the time dimension ref) can be done with a little work with the downside that you cannot use the cube operator (and the CWM2 code is not correct either, but this is less of a priority..if you are not using it). The map to load the fact table must use the table operator and lookups performed for the dimensions using surrogate key references. This is not so bad a loss for those concerned with orphan handling with fact tables since incorporating orphan checking would result in a double lookup when using the cube operator in OWB 10.2.

    To do this:
    1. must add a key on the time dimension table for the DATE column.
    2. can then change the datatype of the column in the fact table referencing the time dimension table to DATE.
    3. then change the foreign key from the fact table to the time dimension table to use the new key on the DATE column.
    4. can now sensibly partition the fact table using time periods.
    5. The fact mapping will now use the fact table (rather than the cube operator) to load directly into the table.

    Cheers
    David

  • RELEVANCY SCORE 3.01

    DB:3.01:Any Impact On Joins dm


    Hi Gurus,We are having some dimension tables and some new columns are added to that dimensions those are not the key columns, so if we create those columns in physical layer and moved those to BMM and Presentation Layer, is there any necessary things to be observe while doing this process and is there any impact for the joins?RegardsVenkatesh

    DB:3.01:Any Impact On Joins dm

    As long as those are not needed in Joins nothing to check.. Just validate the data type in DB. Then after moving to presentation layer just open the column's permission and confirm it matches with the other columns and table.Sometimes i faced this issue, Permissions were missing for the column newlly moved.ThanksAj

  • RELEVANCY SCORE 2.99

    DB:2.99:Distribute With Reference Data Create Additional Rows 3a


    Hi,I have a layout for a single employee. In the lead columns I have 2 characteristics relating to the employee and the 12 posting periods, and in the data columns I have the key figure. I am trying to 'distribute with reference' using the previous year as the reference. It works ok if the the lead columns are the same as the previous year. However, if the lead columns contain different values to the previous year, new rows are created when I execute the distribute function which is incorrect for my purposes. Can anyone advise?Regards,Linda

    DB:2.99:Distribute With Reference Data Create Additional Rows 3a

    Hi Linda,The behavior of the planning function depends also on the settings for the "fields to be changed". 1. Employee Group and staff classification are in the fields to be changed (as well as period) (I presume this is the setting you have):The system will sum up the key figure values for all periods, Employee groups and staff classifications, will look into the reference data and will redistribute the data accordingly. As there are only entries for employee group 1 and staff classification 1 in last years data all values will be written to this employee group and staff classification. This is the entries you see that bears a value. In a layout you will see an entry for employee group 2 and staff classification 2 only until you leave the planning as this is a so-called zero record - all key figures are empty. Such records will be deleted when deleting the BPS buffer upon leaving the planning. If you enter the planning again this entry should be gone.2. Employee group and staff classification are not in the fields to be changed:The system will not change any of these characteristics. It first treats all data for employee group 1 and staff classification 1. As there is no data for this values in the current data the system cannot distribute anything and will continue with employee group 2 and staff classification 2. Now there is current data that is to be distributed but the system does not find any reference data. Hence it cannot determine a distribution key and will leave the data as it is.Thus I think you should delete the characteristics employee group and staff classification from the fields to be changed and should obtain the desired result. If not please get in touch with me again.Best regards,Gerd

  • RELEVANCY SCORE 2.99

    DB:2.99:Olap Ms Analysis Service Cube Related Issues j3


    Hi All
    Background:I have a cube called Factory and its fact table say factory. The fact table has got 2 columns factoryId and factoryType. I am having a dimension table say Mfrfty Association havng 3 columns factoryid, manufactureid and status.The Mfrfty Association is joined to fact table on factoryid. The measure for cube is based on the factoryid. The calulated member is factory count (done using MDX Query).Requirements:Now my requirements is create 2 shared dimensions factory and manufacturerfrom the factory fact table depending upon the factory type (here in this case there are 2 factory types i.e Factory and manufacturer). On creation of the same the factory count will be divided proportionately among the two dimension.Issue:1.When I add the dimension and process the cube error is thrown saying member found in fact table are not present in Dimension manufacturer. here i created the manufacturer dimension by filtering the factoryid whose factory type is manufacturer.2. When I create the manufacturer dimension on Mfrfty Association table on manufactureid the the actual count is getting altered, but the cube gets processed.
    Comments: MDX Query:IIF([Snapshot Q_Y].CurrentMember.Level Is [Snapshot Q_Y].[Year],(Tail(NonEmptyCrossJoin([Snapshot Q_Y].CurrentMember.Children)).Item(0),[Measures].[Supplier Count Base]),CalculationPassValue([Measures].[Supplier Count Base],0))
    Any help on this will be appreciated
    Thanks Reagrds
    Arindam
     
     

    DB:2.99:Olap Ms Analysis Service Cube Related Issues j3

     
    Hi Arindam, I have difficulties understanding your problem but I'll try to answer your questions.
     
    Issue 1. We to set the KeyErrorAction to either ConvertToUnknown (don't forget to set the UnknownMember to Visible if you choose this option) or DiscardRecord (this means that the fact row won't be included in the cube).T.K. Anand explained this on http://msdn.microsoft.com/en-us/library/ms345138.aspx
     
    Issue 2. I'm guessing that the count was altered when you added manufacturer dimension because of the NonEmptyCrossJoin on the calculated member's MDX.
     
    It seems that the decision to create 2 dimensions (factory and manufacturer) depending upon the factory type is an uncommon design. It's probably more common to create a dimension called Factory Type which is linked to the fact table via Factory_Type_Key column. This dimension has attributes like Factory Type ID and Factory Type Name with possible values of Factory and Manufacturer.
     
    To get the count of factory rows and count of measure rows we can then create 2 calculated measures called Factory Count and Manufacturer Count based on the Type Name attribute as follows: [Factory Count] = ([Factory Type].[Factory Type Name].[Factory], [Measures].[Fact Row Count])[Manufacturer Count] = ([Factory Type].[Factory Type Name].[Manufacturer], [Measures].[Fact Row Count])
    Fact Row Count is a measure with AggregateFunction = Count and source = Row binding.
    Alternatively we can add it on the Data Source View's Named Query select statement as follows: select measure1, measure2, 1 as fact_row_count from fact_table.
     
    Kind regards,Vincent

  • RELEVANCY SCORE 2.99

    DB:2.99:Creating Logical Dimension Table Using Common Attributes Of Facts px


    I need to create a common logical dimension based on an attribute present in both the fact tables. Example -
    Expense fact has 3 columns expense_key, expense_amount and customer_name
    Revenue fact has 3 columns revenue_key, revenue_amount and customer_name

    I need to create a prompt for customer_name and have a report including expense_amount and revenue_amount.

    How can I create the logical dimension table in the Repository and join it to both the fact tables?

    Any Suggestions?

    Thanks.

    DB:2.99:Creating Logical Dimension Table Using Common Attributes Of Facts px

    Once you have created the report, create a dashboard prompt on Customer Name and pass that as a filter to the report. This will achieve what you needed.

    Thanks,
    Venkat
    http://oraclebizint.wordpress.com

  • RELEVANCY SCORE 2.98

    DB:2.98:How Can Hide Key Columns From Fact And Dimension In Qvw In Qlikview fm



    Hi,

    I am trying to create Fact and Dimension model in QlikView same like my existing SSAS cube.

    I have DIM_EMP_TABLE (EMP_KEY,EMP_ID, EMP_NAME,JOINING_DATE) another dimension table DIM_DEP_TABLE (DEP_KEY, DEP_ID, DEP_NAME) and Fact table FACT_EMP_DETAILS (EMP_KEY, DEP_KEY, MONTHLY_SALARY, INCENTIVE, SALARY_CREADITED_DATE)

    What I have done is created a QVW file to reload data into 3 different QVD files named as DIM_EMP_DATA.QVD , DIM_DEP_DATA.QVD and FACT_EMP_DETAILS_DATA.QVD

    Now Created another QVW with below query to fetch data from QVD files and display in Listbox and Table chart.

    DIM_EMP_DATA: LOAD EMP_KEY,EMP_ID, EMP_NAME,JOINING_DATE FROM DIM_EMP_DATA.QVD (qvd);

    DIM_DEP_DATA: LOAD DEP_KEY, DEP_ID, DEP_NAME FROM DIM_DEP_DATA.QVD (qvd);

    FACT_EMP_DETAILS_DATA:

    LOAD EMP_KEY, DEP_KEY, MONTHLY_SALARY, INCENTIVE, SALARY_CREADITED_DATE

    FROM FACT_EMP_DETAILS_DATA.QVD (qvd);

    Now here I am seeing based on name automatically it is both dimension joining to fact table.

    Question 1: If 2 tables having same column name but I dont want to create join on them. How to handle this? One way I know create different alias names for both the columns. Any other way to do this?

    Question 2: As EMP_KEY and DEP_KEY are system generated key and should not be exposed to end use because there is no signification of these Keyes to end users. So I want to hide this for all the users. (We have similar concept in SSAS Cube). I would like to know how do I achieve this?

    Please provide me all the steps and appropriate links because I am new to QlikView.

    DB:2.98:How Can Hide Key Columns From Fact And Dimension In Qvw In Qlikview fm


    Hi Waldron,

    How can I do this "you could use the qualify statement prior to your table load. Then all fields are prefixed with the table name"

    Please explain by modifying above query.

  • RELEVANCY SCORE 2.98

    DB:2.98:Type 2 Dimension - Slowly Changing Dimension Question On How To Populate Parentkey zf


    Hi,I am using Slowly Changing Dimension to keep track of history changes. In my dimension table i have, key, ParentKey,...,StartDate,EndDate columns. I use the parentKey column to store the value of the Key column to track the history. The SCD wizard populates the StartDate and EndDate columns in the type2 dimension, but does not populate the ParentId column. Can someone please provide me tips in populating this column using SCD.Thank You,Kalyan Sirsingandla

    DB:2.98:Type 2 Dimension - Slowly Changing Dimension Question On How To Populate Parentkey zf

    No problem.It did seem like that would be the case, since your environment sounded like you were processing a Dimension - but you never know!  The SCD wizard has been used for all kinds of things...

  • RELEVANCY SCORE 2.97

    DB:2.97:Loading Dimension And Facts From Same Source ca



    Hi,

    I have a source file which have data for dimension and facts as well. I want to create a mapping which will have 2 target. One for dimension and other for fact table. As it read the source row it should lookup the dimension to check if the dimension data exists, get the key if it does and proceed towards the fact table target with all the data. However if the dimension lookup does not find the dimension data then insert the dimension data first with a newly generated surrogate key and pass the source row along with the newly generated surrogate key towards the fact table.

    Is there a way to achieve this in the same mapping.

    Thanks.

    DB:2.97:Loading Dimension And Facts From Same Source ca


    Hi ramesh,

    even if the solution will work, then it wont be efficient if the dimension is (Type 2 SCD), also target load plan wont work in this case bcoz the source qualifier is same, but its ok bcoz CONSTRAINT BASED LOADING (dimension tgt 1st and fact tgt 2nd) option is available .. since i mentioned in case type 2 SCD, there will be 4 pipelines (3 for dimension and 2 for fact) , so it will all be messed, its better to seperate the facts and dimension to two different stagging table and then to create the mapping..

    please correct if i am wrong.

    Thanks,

    Jaspreet

  • RELEVANCY SCORE 2.96

    DB:2.96:Create Hierarchy For A Dimension In Oracle Express Administrator 8k


    I have a lookup table called 'Location'. This lookup table has a primary key PKLOC which is made up of 2 columns, State Varchar2(20) and City Varchar2(20). This primary key obviously is a lookup for a dimension of one of my cube and the data in the primary key is hierarchical with 2 levels, namely state and city. I need to import this data into my dimension, Location, using Relational import, in the following form.
    eg. Level 1 Level 2
    ----------- -----------------------
    MD ---- Odenton, Baltimore, Fort Meade
    VA ---- Dulles, Fair Fax

    where MD and VA will come from the column State Varchar2(20) and Odenton, Baltimore, Fort Meade
    Dulles and Fair Fax will come from the column City Varchar2(20).

    The values in my lookup table will look like this
    State City
    ------- ---------------
    MD Odenton
    MD Baltimore
    MD Fort Meade
    VA Dulles
    VA Fair Fax

    how do I import it.

    DB:2.96:Create Hierarchy For A Dimension In Oracle Express Administrator 8k

    I have a lookup table called 'Location'. This lookup table has a primary key PKLOC which is made up of 2 columns, State Varchar2(20) and City Varchar2(20). This primary key obviously is a lookup for a dimension of one of my cube and the data in the primary key is hierarchical with 2 levels, namely state and city. I need to import this data into my dimension, Location, using Relational import, in the following form.
    eg. Level 1 Level 2
    ----------- -----------------------
    MD ---- Odenton, Baltimore, Fort Meade
    VA ---- Dulles, Fair Fax

    where MD and VA will come from the column State Varchar2(20) and Odenton, Baltimore, Fort Meade
    Dulles and Fair Fax will come from the column City Varchar2(20).

    The values in my lookup table will look like this
    State City
    ------- ---------------
    MD Odenton
    MD Baltimore
    MD Fort Meade
    VA Dulles
    VA Fair Fax

    how do I import it.

  • RELEVANCY SCORE 2.94

    DB:2.94:Scd Type 2 Dimension - What Are The Implications Of Using A Surrogate Key As Part Of The Composite Key (Key Columns) Of Attributes Within A Hierarchy Of An Scd Dimension? 8f


    I have a SCD Type 2 Dimension that I am working on that has errors due to the key columns SELECT DISTINCT finding duplicate ids. The data is correct the design of my hierarchy is not. This is due to the key columns not being unique during the
    SELECT DISTINCT, I was recently informed/suggested that using either the surrogate key or combination of start/end date within my key columns would alleviate this issue.
    I have tested this and it worked fine, but I wondered what implications if any there are for using attributes with key columns that are of type changing within an SCD dimension.
    Another question regarding this is what suggested order or place should the surrogate key be within the hierarchy key columns? What affect would this have on the hierarchy.
    I know this is probably fairly vague but hoping someone has some information on this, and it is possible I am just over analyzing this situation but I want to have some information before updating a bunch of my cubes.
    Any advice/information is greatly appreciated.
    Thanks.

  • RELEVANCY SCORE 2.94

    DB:2.94:Advice: Average Denominator As Measure Or Junk Dimension? f9


    The cube I'm working on has 8 measures that are for Averages... My partner I have tested and debated two strategies:
    1.) Calculate the denominator for each of these measures as columns in the fact (1 or 0) then sum as a fact measure and use in the calculation for averages
    2.) Create a junk dimension for each of these denominators (cutting the number of fact measures down from a total of 19 to a mere 11 measures) and then using the tuple (of an existing measure junk dimension member) in the calculation for averages.
    Are there any recommendations or insights as to which way would be best? My partner and I could both go either way. There's very little difference in processing time but the junk dimension adds a few more minutes and more complexity for the ETL
    (looking up the surrogate key in an incremental load)... however, does a junk dimension provide more flexibility for additional measures?
    Thanks for your help!

    DB:2.94:Advice: Average Denominator As Measure Or Junk Dimension? f9

    Hi epiriddy,

    I would think that your first suggestion with multiple measures would be as flexible as the dimension approach; if not more. To add a new measure you would just need to add another two columns - one for your measure and another one for the count, while if
    you are doing the same with the junk dimension, you would have to have the same (or similar) two columns, but then you would have to add the lookup logic to the ETL again. On this note, you may not need to implement lookups at all if the dimension members
    are just two - 1 and 0 and they never change...
    Additionally, dividing two measures will be faster than summing up the dimension member names/values/keys from your junk dimension and then dividing. So, I would say that if you consider performance the non-junk-dim approach is better.
    Boyan Penev --- http://www.bp-msbi.com

  • RELEVANCY SCORE 2.92

    DB:2.92:Poor Performing Parent Child Hierarchy k8


    Q1: Is my dimension and measures designed properly for scd 2 changes in the dimension?
    Q2: If using a parent-child hierarchy is an acceptable approach, why would my query perform so poorly against my customer dimension?
    I have developed a customer dimension with scd 2. Within in the dimension I have two surrogate keys: corporate customer key and customer key and the source system ID: customer ID. I use the corporate customer key to group or relate fuzzy
    matched customer records (based on other customer attributes) and to group or relate the scd 2 changes.

    CustomerCorpKey
    CustomerID
    CustomerKey
    GeographyKey

    CreateTimeStamp

    ExpireTimeStamp

    21
    28
    21
    27937
    29:18.8
    22:14.0

    21
    28
    473359
    27937
    22:14.0
    NULL

    Now my fact table has the customer corp key, customer id, and customer key.

    DateKey
    FullDateAlternateKey
    CustomerCorpKey
    CustomerKey

    CustomerID

    Some Measure

    20100913
    9/13/2010
    21
    21
    28
    $500

    If you notice, the customer dimension has undergone a change. The new customer key is not in the facttable, but I want to keep the historical changes of the customer dimension, but I can nolonger link the fact to the customer dimension
    unless I use the customer corp key and customer key combination. I thought I could usea parent-child dimension on the customer corp key and customer key attributes. The dimension and measures processfine. When browsing on any
    other attributes performance is fantastic, BUT when I drop my customer dimension into the cube browser window it falls apart. I don't even have to have a measure in the browser at the time. The query runs forever. I have 450K customer records
    (this is all records including changes and fuzzy matched customer records). The system customer ID is required by the business to be able to reference against the source system and this is why I keep them in my customer dimension. I used the wizard
    to create the parent-child hierarchy. No other changes were made (i.e. no custom rollups). Browsing the customer dimension runs quickly. This is my second cube, so still relatively new at this; hope this is enough details.
    Thanks.

    DB:2.92:Poor Performing Parent Child Hierarchy k8


    The new customer key is not in the facttable, but I want to keep the historical changes of the customer dimension, but I can nolonger link the fact to the customer dimension unless I use the customer corp key and customer key combination.
    I thought I could usea parent-child dimension on the customer corp key and customer key attributes.

    I dont think it is a parent/children dimension. It seems the CustomerCorpKey
    is the groupID of CustomerKey, so you need to identify a row with both CustomerCorpKey and CustomerKey?

    Generally, the SCD dimension should include a surrogate key which can be used to identify
    each row, I mean it must be unique. But I cannot find a surrogate key in your dimension. You can try to create a named column in DSV, and use it as surrogate key, or use Composite KeyColumns for the key attribute, and then use this key to join fact table
    you will find the join-condition will be two columns:
    http://msdn.microsoft.com/en-us/library/ms175461.aspx

    Hope this helps,
    Raymond
    Raymond Li - MSFT

  • RELEVANCY SCORE 2.92

    DB:2.92:Modelling Dimension Lookup 9c


    Hi,

    I had some questions related to BI repository meta data modeling. We plan to provide a time dimension having details like year, month, week, quarter etc resolved for each date (at a granularity level of day). This dimension table can be looked up based on date key and details can be queried. Now, in our database, a single fact table can have multiple date columns, e.g creation date, completion date, Review date etc . And we want to be able to look the time dimension for each of these date columns. However , i am facing the following issue

    1. In Physical/logical layer, i am not able to specify more than one lookup condition between two tables (i am trying to do it by defining foreign keys between two tables). How can this be achieved ? Is it that i will have create separate logical tables (based on "Time dimension" physical table) for each date column for which i want to look up ? Or is there a separate mechanism for specifying look up details ?

    2. In our tables, the date columns are of DATETIME format whereas for "Time dimension" table, the lookup key should be of DATE only format. I couldn't find a way to specify expression like "trunc(creation_date) = date_key" in the join condition ? Can you please advise on how this can be achieved in repository metadata?

    Also, For providing time dimensions, are there any guidelines from BI for it ? e.g. how the time dimension tables should be populated, best practices etc

    Please advise.

    Thanks and Regards,
    Arif

    DB:2.92:Modelling Dimension Lookup 9c

    Hi,

    I had some questions related to BI repository meta data modeling. We plan to provide a time dimension having details like year, month, week, quarter etc resolved for each date (at a granularity level of day). This dimension table can be looked up based on date key and details can be queried. Now, in our database, a single fact table can have multiple date columns, e.g creation date, completion date, Review date etc . And we want to be able to look the time dimension for each of these date columns. However , i am facing the following issue

    1. In Physical/logical layer, i am not able to specify more than one lookup condition between two tables (i am trying to do it by defining foreign keys between two tables). How can this be achieved ? Is it that i will have create separate logical tables (based on "Time dimension" physical table) for each date column for which i want to look up ? Or is there a separate mechanism for specifying look up details ?

    2. In our tables, the date columns are of DATETIME format whereas for "Time dimension" table, the lookup key should be of DATE only format. I couldn't find a way to specify expression like "trunc(creation_date) = date_key" in the join condition ? Can you please advise on how this can be achieved in repository metadata?

    Also, For providing time dimensions, are there any guidelines from BI for it ? e.g. how the time dimension tables should be populated, best practices etc

    Please advise.

    Thanks and Regards,
    Arif

  • RELEVANCY SCORE 2.91

    DB:2.91:Performance Question f1


     
    Hi ,
     
    We have one fact table and create all dimension through the fact.
    I just wanted to know whether it makes peformance differences or not as following steps:
     
    1. In DSV, just add one fact table, and in cube design I just create all dimensions thorugh the fact using selected column as logical key for each dimension and in dimension usage I joined dimension and fact using that column
     
    2. In DSV, add one fact table and using named query I created views for each dimension and make a relationship. And then create dimension through views which is same fact table but using Distinct like (Select Distinct column_name from Fact table)
     
    Any kinds of advice would be greatly appreciated.
    Thanks in advance.
     
     
     

    DB:2.91:Performance Question f1

    Thanks for your quick response
     

  • RELEVANCY SCORE 2.91

    DB:2.91:Data Warehouse - Dimension Problem s7


    Hi All,

    I didn`t know where to ask my question but I think that this forum would be the best one to try.

    In our project we have to import data into a dimension from a file.
    The problem seems to be easy but data in this dimension is hierarchical and we have no idea how many levels will be in this hierarchy.

    Now we solved this problem by creating hierarchical dimension where ID (surrogate primary key), and PARENT_ID (foreign key to ID) are used.

    The problem is that when a record for parent changes (eg. its name) we have to create new record (type 2 historisation) with DATE_FROM and DATE_TO.
    when we do it we have to do it for all children of this record, all childrens` children and childrens`childrens` children and .....

    this data is later used to populate essbase cube.

    We thought about another solution:
    Each record is historised with type 2 dimension historisation but there is not PARENT_ID key. Instead we used PARENT_ORGINAL_ID key. So record looked like:
    ID (surrogate primary key), ORGINAL_ID (source system primary key), PARENT_ORGINAL_ID, other columns.
    We do no have to (in this solution) split records for children when parents record changes but while building dimension for a cube in essbase we have to generate some logic because one record in essbase cannot point to 2 parents.

    Is there a solution for this problem described?
    I`ve searched Kimball Group site, a couple of books, seminars, lectures and there is always only flattening solution decribed.

    thanks for any help
    oen

    DB:2.91:Data Warehouse - Dimension Problem s7

    Hi,

    If you want the DWH to have history then i think u have to generate child records for every parent and child records for child of child etc....

    the only other option is to use the parent(s) as a source for every child table and as a main driver so that any change in the parent or the child is picked up and loaded into the table. But this will make the load complex..

    Regards
    Bharath

  • RELEVANCY SCORE 2.91

    DB:2.91:Tabular Data Source Dimension Column Configuration aj


    I am trying to configure a tabular(SQL Server table) data source.  Note that I am still pretty new to PPS, so I apologize if this is a silly question.  Also, I am using the Japanese version so some of my lingo might not match up with the English screens but should be close enough.I created a view in the DB, and I am using this as my tabular data source.  Connection and preview works fine.When I am configuring the columns, if the column type is set to Dimension then the Key Column becomes enabled.  What is this column for?My guess was that I had to set another columns type to Key and then when I select the column containing the name I set the column type to Dimension and then set the key column to the Key column I just defined.  Unfortunately when I do this the dimension does not slice, I simply get the aggregate value.(I have verified that the key values are what they should be)  If I leave the key column blank, the Dimension works fine.  The only problem is that I can't guarantee that there are not 2 members with the same name that have different keys, so I would rather define an actual key for the dimension if possible.Any ideas how I can do this?(create a dimension that slices based on a key, but users see the name)Seems like I am missing something pretty simple here, but I can't seem to find the answer so any help would be appreciated.Also, any way to define a compound key?  I am guessing it would be easier to just do it in the database view I created, but if I'm missing something here give me a holler.

  • RELEVANCY SCORE 2.90

    DB:2.90:Storing History Without Using Dimension In Owb c8


    All Gurus,

    Here is the situation.

    Using OWB rel 2, is it possible to populate data in a table that stores history for some of the columns. ( Somehow i dont want to use the DIMENSION )

    If this can be done, please give me an idea.

    For Example :

    Source table - Employee
    empno
    name
    city
    state

    10, 'XYZ', 'Houston', 'TX' -- Initial Record

    Target table emp_target - In this table i would like to create new record if there is a change in CITY source table ( Employee)

    10, 'XYZ', 'HOUSTON', 'TX'
    10, 'XYZ', 'DALLAS', 'TX'

    Thanks in advance and appreciate your time and guidance on this.

  • RELEVANCY SCORE 2.90

    DB:2.90:Case And Alias For Dimension cd


    on columns
    Case when location in ('africa','combodia') then 'Y' end as Flag
    Case when location in ('africa','combodia') then location else 'nothing' end as Pool
    As Dimension
    I want to create two columns with location dimension using case statemnt.mHow can i write an MDX query on columns with case statement and alias.

    Navin.D http://dnavin.wordpress.com

    DB:2.90:Case And Alias For Dimension cd

    WITH MEMBER Locn As
    CASE WHEN ([Geography].[City].CURRENTMEMBER IS [Geography].[City].[Abingdon][ENG]
    OR [Geography].[City].CURRENTMEMBER IS [Geography].[City].[Albany][OR]) THEN
    'Y'
    END
    MEMBER Locn1 As
    CASE WHEN ([Geography].[City].CURRENTMEMBER IS [Geography].[City].[Abingdon][ENG]
    OR [Geography].[City].CURRENTMEMBER IS [Geography].[City].[Albany][OR]) THEN
    [Geography].[City].CURRENTMEMBER.MEMBERVALUE
    ELSE
    NULL
    END
    SELECT {Locn,Locn1} on 0,[Geography].[City].[City] ON 1 from
    [Adventure Works]
    vinu

  • RELEVANCY SCORE 2.89

    DB:2.89:Problem With Sorting Using Discoverer For Olap pd


    Hi,

    How do it create the sort condition such that the Amount column is sorted desc even if a different dimension value is selected. When a different value of the dimension is chosen, the Amount column is always sorted desc without the user clicking on the sort function.

    OracleBI Discoverer 10.1.2.52.25
    Datasource is an OLAP cube.

    Create a simple report
    Page items : Geography, Time
    Columns : Customer and Total Amount
    On Edit worksheet, then on dimension tab, I added a sort order on customer dimension and then added the SORT. Select the SORT MEMBERs by, then created a condition

    Dimension Name ----- Dimension Value
    1) Time ---- Each Time -- the only option I can pick to say that it is applicable for all time dimension values

    2) Geography ----- Each Geography -- again the only option I can pick to say that is applicable for all values in the geography, accross hierarchy

    Sort : on Total Amount desc

    Scenario:
    1) Time = DEC2007, Geography: All
    2) The total_amount column is displayed and sorted properly.

    3) Change the time dimension to NOV2007
    4) The total_amount column isn't showing in the correct sorting.

    Any help is appreciated.
    Thanks

    DB:2.89:Problem With Sorting Using Discoverer For Olap pd

    Hi,

    How do it create the sort condition such that the Amount column is sorted desc even if a different dimension value is selected. When a different value of the dimension is chosen, the Amount column is always sorted desc without the user clicking on the sort function.

    OracleBI Discoverer 10.1.2.52.25
    Datasource is an OLAP cube.

    Create a simple report
    Page items : Geography, Time
    Columns : Customer and Total Amount
    On Edit worksheet, then on dimension tab, I added a sort order on customer dimension and then added the SORT. Select the SORT MEMBERs by, then created a condition

    Dimension Name ----- Dimension Value
    1) Time ---- Each Time -- the only option I can pick to say that it is applicable for all time dimension values

    2) Geography ----- Each Geography -- again the only option I can pick to say that is applicable for all values in the geography, accross hierarchy

    Sort : on Total Amount desc

    Scenario:
    1) Time = DEC2007, Geography: All
    2) The total_amount column is displayed and sorted properly.

    3) Change the time dimension to NOV2007
    4) The total_amount column isn't showing in the correct sorting.

    Any help is appreciated.
    Thanks

  • RELEVANCY SCORE 2.89

    DB:2.89:Problem Creating Hierarchy Based On 2 Physical Dimension Tables 87


    I'm having a problem creating 1 logical dimension with a drill-down hierarchy, based on two separate physical dimension tables. The errors I receive when navigating the drill-down hierarchy is:
    "Cannot find logical table source coverage for logical columns"
    "Missing join between logical tables".

    I'm using OBIEE 10.1.3.4
    Here are the details of what I have setup sofar:

    Physical layer:
    Dimension table DIM_ORG with columns:
    -dimension_key
    -org_total_code
    -org_total_description
    -org_detail_code
    -org_detail_description

    Dimension table DIM_DEPT with columns:
    -dimension_key
    -dept_total_code
    -dept_total_description
    -dept_detail_code
    -dept_detail_description

    Fact table FACT_SALES with columns:
    -fk_org
    -fk_dept
    -sum_sales

    Physical Joins:
    FACT_SALES.fk_org = DIM_ORG_dimension_key
    FACT_SALES.fl_dept = DIM_DEPT.dimension_key

    Business Model Mapping layer:
    I created a logical dimension ORG_DEPT. It contains two logical table sources (DIM_ORG DIM_DEPT) and the following logical columns:
    - All Departments (mapped to dept_total_code)
    - Organisation (mapped to org_detail_description)
    - Organisation Number (mapped to org_detail_code)
    - Department (mapped to dept_detail_description)
    - Department Code (mapped to dept_detail_code)
    The business logical key is based on the combination of Organisation Number Department Code

    The hierarchy I need is: All Departments - Organisation - Department so I created the following hierarchy for ORG_DEPT:
    - Total Level containing: All Departments
    - Organisation Level containing: Organisation Number (defined as the Logical level key) Organisation (defined als the Drill level key)
    - Detail Department Level containing: Department Code (defined as Logical level key) and Department (defined as Drill level key).

    In the LTS of the dimension ORG_DEPT I've set the Content levels for the sources:
    DIM_ORG : Organisation Level
    DIM_DEPT: Detail Department Level
    In the LTS no -inner- joins have been added against related physical tables.

    I created a logical fact table SALES (based on the physical fact table) and joined it against the logical dimension table ORG_DEPT.
    In the LTS the Content level for ORG_DEPT is set against the Detail Department Level. No - inner- joins have been aded against related physical tables.

    When I create a report in Answers to test the hierachy and select only 'All Departments' I get the correct dimension value returned. When I try to drill to the next level I get the following ODBC error:
    "Cannot find logical table source coverage for logical columns: [All Departments]. Please check more detailed level keys are mapped correctly".

    When I create a report in Answers and select both 'All Departments' and 'Sales' I get the correct result. When I try to drill to the next level I get a different ODBC error:
    "Missing join between logical tables DIM_DEPT and DIM_DEPT: There must be at least one physical join link between the underlying physical tables".

    Any suggestions are welcome!

    Thanks!

    DB:2.89:Problem Creating Hierarchy Based On 2 Physical Dimension Tables 87

    Hello Robert,

    Your suggestions were known to me but I still wanted to combine the two physical dimension tables in one logical dimension. So I've played around a bit more and found the solution: In my original setup I had two seperate logical table sources (one for each physical dimension table). The solution was to combine the two logical table sources in one logical table source. I achieved that by logical joining the DIM_DEPT table to the FACT_SALES table and subsequently to the DIM_ORG within the 1 LTS and using inner joins.

    Then I created the logical table key (a combination of org_detail_code dept_detail_code). After that I could create the hierarchy with no problem.

    Edited by: The_Dutchman on Nov 4, 2011 9:43 PM

  • RELEVANCY SCORE 2.88

    DB:2.88:Use A Dimension More Than Once dp


    Is it possible or good/bad practice to use a Dimension more than once in the Cube?For example I have about ten columns in my original OLTP table which hold Yes or No answers. Do I create a dimension for each one of these columns or just use one dimension with a surrogate key linking Mr Shaw

    DB:2.88:Use A Dimension More Than Once dp

    it is possible. Such dimension are called role playing dimension. Date dimension is very common of that type.
    from Chris Wade's blog @ http://blogs.conchango.com/christianwade/archive/2005/04/07/1255.aspxA single dimension can be related to a set of facts through multiple roles.  For example, FactOrderDetails can have order date, a ship date, and a receipt date.  In this case our time dimension fulfils 3 roles with respect to dimensioning orders.
     
    The way this is represented in a Data Source View is with a foreign key relationship for each role.  As shown by the following screenshot, there are 3 foreign-key relationships between FactInternetSales and DimTime.  One is selected and the columns forming the relationship are displayed.  In this case the source column is ShipDateKey, but the other foreign keys use OrderDateKey and DueDateKey respectively.
     

     
    The only way to do this in Analysis Services 2000 was to implement a separate dimension for each role, which results in data replication and management overhead such as extra processing time etc.  In fact in Analysis Services 2005, each role actually appears as a separate dimension to the end user.  But in terms of storage, maintenance and design, there is significantly less overhead.Sometimes Junk dimension like ones having YES,NO or TRUE , FALSE are also used this why where same dimension has more than one relation to the Fact table.Hope this helpsAshwani Roy
    My Blog
    Please click the Mark as Answer button if a post solves your problem!

  • RELEVANCY SCORE 2.88

    DB:2.88:Dimension Index Strategy For Performance (Star Schema) cf


    We have many dimensions which have the following:

    Primary Keys (PK) (Generated Sequence Number Column)
    Unique Keys (UK) (Columns that make the Dimension Unique)

    The UK was created to support ETL updates.
    The PK was create to support the Star Schema structure.

    Now, we are looking at adding indexes to help optimize 'where' criteria applied
    to a Dimension, hanging off the Star Schema Fact Table.

    We have considered adding a simple index based on just the raw dimension attribute as well as creating a concatenated key composed of the raw attribute and the PK.

    For instance. Dimension A has a PK as well as five attributes Attr-1 thru Attr-5. We see lots of queries with a where clause like "...where Attr-3 = 'abc'...."

    In general, would it be better performance-wise if we added another
    index to the dimension composed strictly of the raw attribute or is it better to add a concatenated index containing the columns of the attributes plus the PK?

    As we are using Star Schema, this is a Data Warehousing specific question. Also, we have a BI tool in front of the DBMS, so we cannot intervene and add hints.

    Any feedback would be appreciated.

  • RELEVANCY SCORE 2.88

    DB:2.88:What's The Difference: Defaulttablemode, Arraylist, Vector? fx


    Hi,
    Trying to find the best way to store data (multiple columns and rows). Key things about my data:
    1) Different data type for each column (string, int, float)
    2) Data value will change constantly with new data
    3) Table needs to be able to add more columns and rows dynamically.

    I got suggestions using ArrayList, DefaultTableModel, Vectors, etc..I've gone thru the API and still confuse of the difference. Can someone explain when to use what? I'm migrating from C and all i'm looking for is an multi-dimension array that can be expand dynamically. Thanks!

    DB:2.88:What's The Difference: Defaulttablemode, Arraylist, Vector? fx

    You could access row2, column1 by saying

    ((Vector)T.elementAt(2)).elementAt(1)

    Since the return type is Object, consider casting to the type you want.

    Thanks

  • RELEVANCY SCORE 2.88

    DB:2.88:Difference Between Dimension Id And Dimension Key sc


    I am reading the third chapter of Rittman's OBIEE book on design of Repository.

    It says while building the BMM, don't drag the dimension Id columns from physical layer to logical layer as the BI Server takes care of the join for you but make sure you drag across the dimension key columns as you will need these later on to create your logical table keys.

    Can someone enlighten me on what is the difference between a dimension id and dimension key (may be with an example).

    Thanks.

    DB:2.88:Difference Between Dimension Id And Dimension Key sc

    I am reading the third chapter of Rittman's OBIEE book on design of Repository.

    It says while building the BMM, don't drag the dimension Id columns from physical layer to logical layer as the BI Server takes care of the join for you but make sure you drag across the dimension key columns as you will need these later on to create your logical table keys.

    Can someone enlighten me on what is the difference between a dimension id and dimension key (may be with an example).

    Thanks.

  • RELEVANCY SCORE 2.86

    DB:2.86:#Datasync Error 71



    Helloo Guys,

    I am working on report which have to fetch the data from two data providers Bex Query and Excel.

    1) In bex query I have taken some feilds for example : VPU-Key, Region-Key ..................................... which are all detail objects and also some measures. I have ran the report and got the data.

    2) I have taken data from excel which have columns like VPU-Key(Dimension) and some other dimension columns.

    I have merged the VPU-Key from Bex query and VPU-Key from Excel data. When I tried to add the other columns from th4e Bex query it is showing an error like #DataSync error.

    Can you please help out for any solutions.

    Final output should be

    VPU-Key and all the other objects from Bex and Excel

    Regards,

    Eswar

    DB:2.86:#Datasync Error 71


    Hello,

    you have to merge also "all other" dimensions, not only the key. Refer here for more information on merging:

    http://help.sap.com/businessobject/product_guides/boexir4/en/xi4sp5_ia_en.pdf#page=247

    BR

  • RELEVANCY SCORE 2.85

    DB:2.85:Regarding (Base Level For International &Amp; Total For Local) Urgent mm


    Hi,

    b) The country dimension should be a 2-level hierarchical dimension (Base level a "Total Level") In AWM

    I need to seperarte it that base level for international and total level for local . Can you please help me on this how to create in AWM how to solve this problem this is very very urgent.

    In AWM:

    Create new dimension 'COUNTRY'

    Create a new hierarchy, belonging to this dimension

    Create two levels for this hierarchy, called TOTAL and BASE

    Map TOTAL BASE levels in dimension to columns in table/view

    Right-click COUNTRY dimension-Maintain dimension-maintain wizard

    Right-click hierarchy-view data: should display a two-level hierarchy

    Thanks,
    Vel

    DB:2.85:Regarding (Base Level For International &Amp; Total For Local) Urgent mm

    Hi Aaron White,

    The problem is same but can you help me on this it is curgent:

    I need to seperarte it that base level for international and total level for local . Can you please help me on this how to create in AWM how to solve this problem this is very very urgent. b) The country dimension should be a 2-level hierarchical dimension (Base level a "Total Level") In AWM

    Thanks,
    Vel

  • RELEVANCY SCORE 2.85

    DB:2.85:Chart Problem In Bo Reporting While Using With Two Date Columns ma



    Post Author: Saravanan Perumal

    CA Forum: Desktop Intelligence Reporting

    Hi,

    We have an issue with BusinessObject report. The report consists of 3 columns. Two columns are date columns. I have to create chart between two date columns. But the two date columns are dimension objects. So we wonu2019t create chart using 2 dimension objects. So, I have created one variable as u201CTestu201D for one date column. But I got the chart display is not proper. As per my understanding if we create the chart based on the above solution it won't come as proper chart. Kindly suggest on this issue. Thanks in advance!

    Br

    Saravanan.

    DB:2.85:Chart Problem In Bo Reporting While Using With Two Date Columns ma


    Post Author: Saravanan Perumal

    CA Forum: Desktop Intelligence Reporting

    Hi,

    We have an issue with BusinessObject report. The report consists of 3 columns. Two columns are date columns. I have to create chart between two date columns. But the two date columns are dimension objects. So we wonu2019t create chart using 2 dimension objects. So, I have created one variable as u201CTestu201D for one date column. But I got the chart display is not proper. As per my understanding if we create the chart based on the above solution it won't come as proper chart. Kindly suggest on this issue. Thanks in advance!

    Br

    Saravanan.

  • RELEVANCY SCORE 2.85

    DB:2.85:Adding Values In 2 Dimensions In Qlikview aj



    I have 2 dimensions and would like to add values from both columns to create a third dimension. I tried using 'add calculated dimension' but the result is always error. Can somebody show me the formula to calculate this? its basically ----- dimension1 + dimension2 = dimension3

    Thanx for your help.

    DB:2.85:Adding Values In 2 Dimensions In Qlikview aj


    Thats exactly what i did. =dimension1 +dimension2 but it gave error in calculation...

    the 2 dimensions are in the same table side by side...I'll follow ur advice and post a sample qvw

  • RELEVANCY SCORE 2.85

    DB:2.85:Time Series Model Error 9x


    Time in my underlying data is represented as two columns [Year] Char(4), [Month] Char(2). I set them up as the key for my model. The wizard shows them as being set to key. However I get this error:"Error (Data mining): The usage for the 'YEAR MONTH' column of the 'Time Series Model' mining model must be set to Key, because its source mining structure column is a key column."What does the error mean? Does the Key need to be the key of the time dimension or the key of the the fact tables?I'm using SQL2005 June CTP.Matthew Martin

    DB:2.85:Time Series Model Error 9x

    You will need a single continuous-valued column to act as the key (key time) of the mining structure.The answer to the question about dimension and fact table depends on your data and the problem you are trying to solve.For instance, if your data looks like thisTime Product Sales1 Milk 201 Bread 152 Milk 222Bread 14...You would mark Time as KEY (which will become KEY TIME if you look at the column in the designer) and Product as KEY.

  • RELEVANCY SCORE 2.84

    DB:2.84:Question On Deal With Very Wide Kinf Of Fact Table f1


    Hi There,
    We have exsiting data mart which ETL data from different data sources, there is one big and wide history table which kind of like fact table but, there is no suragate key, it consists of measures and lots of descriptive columns, for example,
    AcctNo LoanType PmtStatus DefaultStage IntRate InitCollectionDate LatestCollDate Amount Active
    N123 Type1 Unpay Start 4.5 01/01/2000
    08/30/2011 3223.2 Yes
    N124 Type2 Pay Start 6.5 01/01/2005
    09/01/2011 6223.2 No
    ...
    1) There are lots of columns, the question is that if I want to use this as fact table, should I make bunch of dimension table such as dimType, dimFlag, using the column in fact table to join the dimension table even dimension table only has two records
    (for example , dimFlag (Yes, No)?
    2) should I leave these descripitive columns, not creating too many small dimension tables to join?
    3) should Ire-do etl and use surgate key to populate fact table?
    I would like to ask you suggestion regarding this table, should I break it, whiDon

    DB:2.84:Question On Deal With Very Wide Kinf Of Fact Table f1

    Hi Don

    There might be some scenario's where that is true.
    But generally on a DWH that supports aggregating, and grouping queries you would want to move all the descriptive elements to dimension tables.

    1. It's easier to maintain, imagine that every time a descriptive element changes that you need to update thousands of fact rows instead of 1 dimension row. This is data modelling 101.

    2. Doing grouping and aggregating on dim attributes should be quicker depending on the scenario and complexity of the query:
    I tested a scenario on my local machine.
    I took a fact table with 6 million rows. This had both a date surrogate key to join to a date dimension and a matching date data type column.
    First I cleared the cache:

    CHECKPOINT;
    GO
    DBCC DROPCLEANBUFFERS;
    DBCC FREESYSTEMCACHE(ALL)
    DBCC FREESESSIONCACHE
    DBCC FREEPROCCACHE

  • RELEVANCY SCORE 2.84

    DB:2.84:Date/Time Dimension kk


    Hi All,
    We have created a Data Warehouse but we don't have a date/time dimension yet.
    1. Do you guys know of any script that we can execeute to create a Time dimension?
    2. Also, what should I use for primary key in the date/time dimesnion? A surrogate key?
    Please advise, thanks for your help.

    DB:2.84:Date/Time Dimension kk

    Well, the reason is the data type size. If you have datetime it is 8 bytes, while an int is 4. For a large fact table this may be significant. In general, SSAS works best with integer keys. Having said that, with SQL 2008 you have the date data type, which
    is also 4 bytes, which is the same as the int. Still, int is the best option because of the way SSAS works internally. Having said that, this is a best-practice advice, not a mandatory practice - if you have the chance you can convert it and store it as an
    int. If too inconvenient you can stick to the datetime.
    Boyan Penev --- http://www.bp-msbi.com

  • RELEVANCY SCORE 2.84

    DB:2.84:How To Create Dimension From Two Column Source Table? cz


    I have a stub table that will be extended in future. At present the source table only has 2 columns: surrogate key business key

    How do I create a dimension from this without getting level errors?
    How do I get the business key to be used as both the short and long description?

    (using 10.0.2.0.1)

    Thanks in advance
    user592258

    DB:2.84:How To Create Dimension From Two Column Source Table? cz

    Not sure I understand this completely but I assume you have two attributes (lets say name and businesskey). You can simply map the business key into both and that should do the trick.

    JP

  • RELEVANCY SCORE 2.83

    DB:2.83:Urgent: Unable To Create Cube In Analysis Manager From Text File mp


    Hello,I am trying to create a dimension/cube from a text file. I have created Data Source in Analysis Manager using "Microsoft Text Provider" for creating the connection string. I have created a System DSN. But when i select this data source and then select my text file for cube/dimension creation, it gives me following errors:1. Unable to retrieve the list of columns for the Test.txt table. 2. Can not get the list of columns from the Test.txt table in the TextProvider Data Souce( where TextProvider isthe system DSN and Test.txt is the text file containing data to be loaded).Can anybody help me to resolve this error.Thanks in advance,

    DB:2.83:Urgent: Unable To Create Cube In Analysis Manager From Text File mp

    Hello,I am trying to create a dimension/cube from a text file. I have created Data Source in Analysis Manager using "Microsoft Text Provider" for creating the connection string. I have created a System DSN. But when i select this data source and then select my text file for cube/dimension creation, it gives me following errors:1. Unable to retrieve the list of columns for the Test.txt table. 2. Can not get the list of columns from the Test.txt table in the TextProvider Data Souce( where TextProvider isthe system DSN and Test.txt is the text file containing data to be loaded).Can anybody help me to resolve this error.Thanks in advance,

  • RELEVANCY SCORE 2.83

    DB:2.83:Olap Ms Analysis Service Cube Related Issues 91


    Hi All,
    Background:I have a cube called Factory and its fact table say factory. The fact table has got 2 columns factoryId and factoryType. I am having a dimension table say Mfrfty Association havng 3 columns factoryid, manufactureid and status.The Mfrfty Association is joined to fact table on factoryid. The measure for cube is based on the factoryid. The calulated member is factory count (done using MDX Query).Requirements:Now my requirements is create 2 shared dimensions factory and manufacturerfrom the factory fact table depending upon the factory type (here in this case there are 2 factory types i.e Factory and manufacturer). On creation of the same the factory count will be divided proportionately among the two dimension.Issue:1.When I add the dimension and process the cube error is thrown saying member found in fact table are not present in Dimension manufacturer. here i created the manufacturer dimension by filtering the factoryid whose factory type is manufacturer.2. When I create the manufacturer dimension on Mfrfty Association table on manufactureid the the actual count is getting altered, but the cube gets processed.Comments: MDX Query:IIF([Snapshot Q_Y].CurrentMember.Level Is [Snapshot Q_Y].[Year],(Tail(NonEmptyCrossJoin([Snapshot Q_Y].CurrentMember.Children)).Item(0),[Measures].[Supplier Count Base]),CalculationPassValue([Measures].[Supplier Count Base],0))
     
    Any help will be appriciated.
     
    Thanks,
    Arindam

    DB:2.83:Olap Ms Analysis Service Cube Related Issues 91

    Hi All,
    Background:I have a cube called Factory and its fact table say factory. The fact table has got 2 columns factoryId and factoryType. I am having a dimension table say Mfrfty Association havng 3 columns factoryid, manufactureid and status.The Mfrfty Association is joined to fact table on factoryid. The measure for cube is based on the factoryid. The calulated member is factory count (done using MDX Query).Requirements:Now my requirements is create 2 shared dimensions factory and manufacturerfrom the factory fact table depending upon the factory type (here in this case there are 2 factory types i.e Factory and manufacturer). On creation of the same the factory count will be divided proportionately among the two dimension.Issue:1.When I add the dimension and process the cube error is thrown saying member found in fact table are not present in Dimension manufacturer. here i created the manufacturer dimension by filtering the factoryid whose factory type is manufacturer.2. When I create the manufacturer dimension on Mfrfty Association table on manufactureid the the actual count is getting altered, but the cube gets processed.Comments: MDX Query:IIF([Snapshot Q_Y].CurrentMember.Level Is [Snapshot Q_Y].[Year],(Tail(NonEmptyCrossJoin([Snapshot Q_Y].CurrentMember.Children)).Item(0),[Measures].[Supplier Count Base]),CalculationPassValue([Measures].[Supplier Count Base],0))
     
    Any help will be appriciated.
     
    Thanks,
    Arindam

  • RELEVANCY SCORE 2.83

    DB:2.83:Best Way Of Using Indexes? cx


    Hi There,
    I tried to load a temporary table where I have lot of information coming from source tables and in this temp table I do some lookups or merges with dimension tables to updates key information required for fact table in the temp table.

    My Question is I have to create indexes on some of the columns from source but which way is good. Creating indexes while creating table and load or load data and then create indexes?
    what are the implications of both on db?

    DB:2.83:Best Way Of Using Indexes? cx

    Hi,
    I think it's depends on number of rows you are inserting. I would prefer to have cluster index created in ever increasing column so that you end up adding new rows always at the end in B-Tree. If Cluster index is not on ever increasing column then It's good
    idea to insert all rows first then create Cluster index.
    I would not prefer to have Non-Cluster index while you perform DML operations. If you see your Execution plan you can see that it will update your Cluster Index (if any) or heap first then also update your Non-Cluster index.
    I have faced scenario where i was inserting data into same table from which i was selecting. Only God can help you in that condition :)

    Cheers,
    Nimesh

  • RELEVANCY SCORE 2.83

    DB:2.83:Epma For Attribute Dimension jk


    What is the column field heading for Attribute dimension.

    for example if I have Entity dimension and I have a Geography Attribute dimension attached to it and I have a Color Attribute dimension attached to it as well

    What would be the column names for the 2 columns in the entity section be?

    I'm using the Oracle Excel EPMA file to create the ads but I could hardcode it as well.

    DB:2.83:Epma For Attribute Dimension jk

    Hi,
    The column headers should be Geography and Color. You should also define the associations between entity and these attribute dimensions in associations sheet.
    Actually, these columns should appear automatically after you define the association.

    Cheers,
    Alp

  • RELEVANCY SCORE 2.83

    DB:2.83:Name Columns And Null Values In Snowflake Schema d9



    I have created my first cube and am having a small issue I hope someone might be able to assist me with. I started using SSAS a few days ago. So, please excuse me
    if I get any terms wrong.

    I am using a snowflake schema.
    I have simplified the schema for purposes of this question as follows: a L1 dimension (Opportunity) which is related to two L2 dimensions (Business Unit and Technology) as follows:

    [Technology]
    [Opportunity]
    [Business Unit]

    Every Opportunity is associated with a Business Unit (the underlying column is not nullable).

    Not every Opportunity is associated with a Technology (the underlying column is nullable).

    Once I started looking at the cube in Excel, I noticed that holding the mouse over an Opportunity would show the L2 dimensions as keys (e.g. Business Unit as 1, 2
    and 3) as opposed to names (e.g. Business Unit as New York, Los Angeles and 'Chicago').
    So, I just assigned the Name Column for the Business Unit Key attribute in the Opportunity dimension and everything worked out great. But, nothing I have been able to figure out will allow me to do the same for Technology.
    I really do not want to use a named query to collapse the Business Unit dimension into the Opportunity dimension for a variety of reasons.

    Can someone provide me some guidance in assigning a name column for a nullable dimension attribute?
    I have tried all the options for NullProcessing on the name and key columns for the technology key in the opportunity dimension.
    I am beginning to think I am missing something basic.
    Any assistance would be greatly appreciated.
    -Robert

    DB:2.83:Name Columns And Null Values In Snowflake Schema d9

    yeah....the other options that u already utilized...or else handle it in the database itself....Could you please it as answer..Thanks, Tanmoy Santra

  • RELEVANCY SCORE 2.83

    DB:2.83:Drilldown Using A Composed Level Key ks


    Hi, I'm new with OBIEE, I have this Dimension Table:

    ORDERS(order_key, customer_name, customer_surname, customer_city)

    I'd want to build a dimension with 3 levels, from the root to the leaf: CUST_CITY - CUST_NAME - ORDER_KEY
    where the Answers Column CUST_NAME that I want to use for drilldown would be composed by name+surname, which are 2 separate logical columns... is it possible? how could I do?

    any suggestion will be appreciated, thanks!

    DB:2.83:Drilldown Using A Composed Level Key ks

    Thank you very much Ed, I couldn't find the concatenation symbol between 2 columns... it was the pipe as you said and ' ' for the blank space!

  • RELEVANCY SCORE 2.83

    DB:2.83:Problem With Multiple Keys To Same Dimension zj


    Hi,
    I have a fact which has multiple foreign keys to the time dimension . Request date, ship_date, invoice_date, etc..
    The problem is owb lets me create these multiple foreign keys but when I generate these out It names the columns in the fact/cube as time_id,time_id1 and time_id2. It does not let me change the name. If I go into properties and change the name It only changes the name of the foreign key not the name of the columns in the table that is generated.
    Is there any way to rename the columns in the table generated for the fact?

    DB:2.83:Problem With Multiple Keys To Same Dimension zj

    Thank you. I was choosing editor but not from the menu. I guess that makes a difference.
    Your help was really appreciated.

  • RELEVANCY SCORE 2.83

    DB:2.83:How To Create Surrogate Key In Dimension Without Unique Value 3p


    Hi, I have a dimension where there is no column with unique value. I want to add a surrogate key to replace the existing primary key which is derived from concatenating 3 columns(e.g. 'A'||'B'||'C'). I'm thinking of using sequence. But this won't allow me to link the dimension to fact table. How do I come up with surrogate key under this situation? Thanks. ~Tracy

    DB:2.83:How To Create Surrogate Key In Dimension Without Unique Value 3p

    You will need either D-code or the surrogate key in the dimension to be able to match. So if you generate the SK in the module you use to load your datamart from and then use it as PK in you dimension it should be possible.
    You could also create a view that combines this SK/D-code translation with all other information necessary to load the dimension, this way you can map all view-columns (minus D-code then if you don't want that in your dimension) 1:1 to the dimension with the attribute properties I mentioned earlier.

    By the way, if you have a column in any dimension you don't want end-users to see, simply hide it in your reporting environment.

    Good luck, Patrick

  • RELEVANCY SCORE 2.82

    DB:2.82:Time Dimension 13


    Hi,

    I am using OBIEE out of the box repository and if I run a report using "Cal Year" from W_MCAL_YEAR_D dimension and some columns from Fact table, I see the data in the columns I selected from the fact table but I don't see data under "Cal Year" column. However if I aslo select another column "Cal Qtr" from W_MCAL_YEAR_D dimension, then I am able to see the data in all the columns.
    Also if I put a filter in the "Cal Year", I am able to see the data in all the columns.
    Can you please tell me why I can not see the data for "Cal Year" if I select that with other columns from Fact table?
    I notice one thing that Dimensional Hierarchy corresponding to W_MCAL_YEAR_D is selected as Time Dimension and Cal_Year_Wid is selected as a chronological key (which is not the lowest level of the Hierarchy).

    Thanks a lot.

    DB:2.82:Time Dimension 13

    Thank you Joe, I will check the levels of the fact columns and will get back to you.

  • RELEVANCY SCORE 2.82

    DB:2.82:Issue While Programmatically Slowly Chaging Dimension. f3


    Hi,
    I am working on Creating an SSIS package to add Slowly Chaging Dimension to the package programmatically.
    I have done the following steps:
    1.           Choosing the connection manager to access the data source that contains the dimension table that you want to update.
      You can select from a list of connection managers that the package includes.
    2.           Choosing the dimension table or view you want to update.
    After you select the connection manager, you can select the table or view from the data source.
    3.           Setting key attributes on columns and map input columns to columns in the dimension table.
    You must choose at least one business key column in the dimension table and map it to an input column. Other input columns can be mapped to columns in the dimension table as non-key mappings.
    4.           Choose the change type for each column.
    o                                    Changing attribute overwrites existing values in records.
    o                                    Historical attribute creates new records instead of updating existing records.
    o                                    Fixed attribute indicates that the column value must not change.
    Code://Set the Key Element as part of Creating the SCD Transformation:
    instance.SetInputColumnProperty(ID, this.SlowlyChaningDim.InputCollection[0].InputColumnCollection[1].ID, ColumnType, 1);
    //Set one of the ColumnType Properties as Changing Attribute Updates Output
    instance.SetInputColumnProperty(ID, this.SlowlyChaningDim.InputCollection[0].InputColumnCollection[15].ID, ColumnType, 2);
    //Set one of the ColumnType  Properties as Historical Attributes Updates output.
    instance.SetInputColumnProperty(ID, this.SlowlyChaningDim.InputCollection[0].InputColumnCollection[85].ID, ColumnType, 3);
     
    Now i create an OleDbCommand Transformation to capture Changing Attribute Updates (Type-1) output.
     
    this.OledbCommand1 = this.dataflow.ComponentMetaDataCollection.New();
    this.OledbCommand1.ComponentClassID = DTSTransform.OleDBCommand.1;
    this.OledbCommand1.Name = OledbCommand1;
    this.OledbCommand1.Description = ChangingColumnAttribute;

    CManagedComponentWrapper oledbinstance = this.OledbCommand1.Instantiate();
    oledbinstance.ProvideComponentProperties();

    this.OledbCommand1.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[OLEDBDestination].ID;
    this.OledbCommand1.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[OLEDBDestination]);
    oledbinstance.SetComponentProperty(CommandTimeout, 0);
    oledbinstance.SetComponentProperty(DefaultCodePage,1252);

    StringBuilder sbquery = new StringBuilder();
    sbquery.Append(UPDATE SIRWorkdm..[Engagement] SET [BillingType] = ? WHERE [EngagementId] = ?); //Here BillingType is the ChangingColumnAttribute and EngagementId is the key
    oledbinstance.SetComponentProperty(SqlCommand, sbquery.ToString());
    ---- Need inputs as to how to map the Source and ExternalMetadataColumns.
    ---------  -------- ------------
    this.dataflow.PathCollection.New().AttachPathAndPropagateNotifications(this.SlowlyChaningDim.OutputCollection[3], this.OledbCommand1.InputCollection[0]);
     
     
    Please advice as to how to proceed further
    Thanks,
    Sriharsh
     
     

    DB:2.82:Issue While Programmatically Slowly Chaging Dimension. f3

    Hello sir,
    I am creating scd task programatically. Package created successfully using c# code but in package, scd task did not show table name, connection manager.
    Can you please post your c# code to generate slowly changing Dimension task?
    this helps me.
    thank you.

  • RELEVANCY SCORE 2.82

    DB:2.82:Level 2 Funcitionally Dependent And Not Consistent 13


    I'm trying to create a dimension table but get the following error when i try to add a child dimension:

    BUSINESS MODEL CCCi PS Fin Analyitcs:
    [nQSError: 15001] Could not load navigation space for subject area CCCi PS Fin Analyitcs.
    [nQSError: 15019] Table PeopleSoft FIN Tree Node Hierarchy is functionally dependent upon level Node Level 1 Hierarchy, but a more detailed child level has associated columns from that same table or a more detailed table.

    Here is the situation. I have a single physical table (Table A) that contains data that makes up a hierarchy. Here are the key fields: (1) Node Name, (2) Node Level (1 high - 9 low), and (3) Parent Node Name. I want to create a dimension that allows me to drill down from level 1 to level 9 for node details. Initially I've tried to set this up to just drill down from level 1 to level 2.

    What I've done:
    (1) Created two aliases for Table A: 1A and 1B in the Physical Layer
    (2) Created a complex join between Tables 1A and 1B where Parent Node Name of 1B = Node Name of 1A and 1B Node Level = 2.
    (3) Created a Logical table and moved the three fields (from above) from table 1A to the logical table.
    (4) In the logical table source I added the 1B table as a new table and joined it to 1A using a Left Outer join
    (5) Added 3 columns from 1B to logical table
    (6) Selected create dimension from the logical table

    This created a dimension with the 3 fields (total of 6) from 1A and 1B on the first level. I do a Check Consistency at it passes. However, i really want the 3 fields from 1B to be on a child dimension.

    (7) I create a child dimension and drag the 3 fields on level 1 from 1B to level 2 (the child level).
    (8) Check consistency and i get the message from above.

    I'm not sure why i cannot do this. If you can help solve this or provide an alternative solution it would be helpful.

    Thanks...

    DB:2.82:Level 2 Funcitionally Dependent And Not Consistent 13

    Hi,

    As i mentioned earlier your logical table key should be at the lowest level of your hierarchy.There is no need to make it as a hierarchy key.But it should be added at the lowest level.Add logical table keys to lowest level of hierarchy and check other things mentioned in the link provided in earlier post.

    Regards,
    Sandeep

  • RELEVANCY SCORE 2.82

    DB:2.82:Olap Ms Analysis Service Cube Related Errors 1s



    Background:I have a cube called Factory and its fact table say factory. The fact table has got 2 columns factoryId and factoryType. I am having a dimension table say Mfrfty Association havng 3 columns factoryid, manufactureid and status.The Mfrfty Association is joined to fact table on factoryid. The measure for cube is based on the factoryid. The calulated member is factory count (done using MDX Query).Requirements:Now my requirements is create 2 shared dimensions factory and manufacturerfrom the factory fact table depending upon the factory type (here in this case there are 2 factory types i.e Factory and manufacturer). On creation of the same the factory count will be divided proportionately among the two dimension.Issue:1.When I add the dimension and process the cube error is thrown saying member found in fact table are not present in Dimension manufacturer. here i created the manufacturer dimension by filtering the factoryid whose factory type is manufacturer.2. When I create the manufacturer dimension on Mfrfty Association table on manufactureid the the actual count is getting altered, but the cube gets processed.
     
    Comments: MDX Query:IIF([Snapshot Q_Y].CurrentMember.Level Is [Snapshot Q_Y].[Year],(Tail(NonEmptyCrossJoin([Snapshot Q_Y].CurrentMember.Children)).Item(0),[Measures].[Supplier Count Base]),CalculationPassValue([Measures].[Supplier Count Base],0))

    DB:2.82:Olap Ms Analysis Service Cube Related Errors 1s


    Background:I have a cube called Factory and its fact table say factory. The fact table has got 2 columns factoryId and factoryType. I am having a dimension table say Mfrfty Association havng 3 columns factoryid, manufactureid and status.The Mfrfty Association is joined to fact table on factoryid. The measure for cube is based on the factoryid. The calulated member is factory count (done using MDX Query).Requirements:Now my requirements is create 2 shared dimensions factory and manufacturerfrom the factory fact table depending upon the factory type (here in this case there are 2 factory types i.e Factory and manufacturer). On creation of the same the factory count will be divided proportionately among the two dimension.Issue:1.When I add the dimension and process the cube error is thrown saying member found in fact table are not present in Dimension manufacturer. here i created the manufacturer dimension by filtering the factoryid whose factory type is manufacturer.2. When I create the manufacturer dimension on Mfrfty Association table on manufactureid the the actual count is getting altered, but the cube gets processed.
     
    Comments: MDX Query:IIF([Snapshot Q_Y].CurrentMember.Level Is [Snapshot Q_Y].[Year],(Tail(NonEmptyCrossJoin([Snapshot Q_Y].CurrentMember.Children)).Item(0),[Measures].[Supplier Count Base]),CalculationPassValue([Measures].[Supplier Count Base],0))

  • RELEVANCY SCORE 2.82

    DB:2.82:Drop Columns And How To Add Them Back To The Same Position d9


    Hi,

    My requirement is, I want to drop columns in the existing table and later on, when required I want to add those columns at the same position.

    1) Create t_emp table

    create table t_emp(
    empno varchar2(10) primary key,
    ename varchar2(100),
    desig varchar2(100),
    dept varchar2(100),
    salary number(10,2));

    2) Dropping the below columns,

    alter table t_emp
    drop (desig, dept);

    3) Add those columns again.

    alter table t_emp
    add (desig varchar2(100),
    dept varchar2(100))

    4) desc t_emp

    Now, the columns order is changed, I would like to bring to the original columns position, that is,

    empno, ename, desig, dept, salary

    I'm trying through few work-around for this, like using a swap table. Please share your knowledge and what would be the apt method.

    Thank you.

    DB:2.82:Drop Columns And How To Add Them Back To The Same Position d9

    I don't think there is any direct method for doing this but there is an alternative if your object is not editioned (which I assume is not there).
    You can use dbms_redefinition package for this purpose.
    You have to create an intermediate table as per the desired structure.
    After that you have to start up the process of redefinition somewhat like below:

    DBMS_REDEFINITION.can_redef_table(SchemaName, orig table name);

    DBMS_REDEFINITION.start_redef_table(SchemaName, orig table name, intermediate table name);

    DBMS_REDEFINITION.sync_interim_table(SchemaName, orig table name, intermediate table name);

    DBMS_REDEFINITION.finish_redef_table(SchemaName, orig table name, intermediate table name);

    After these steps you can drop the intermediate table.

    Hope this helps!!!
    Cheers,
    ASI

    Edited by: user3947812 on May 8, 2012 5:37 AM

    Further to this you can also use COPY_TABLE_DEPENDENTS to copy the dependent objects of the tables..

  • RELEVANCY SCORE 2.82

    DB:2.82:How To Create Multi-Column Primary Key Using 10g Xe? sz


    How to create multi-column compound primary key using 10g XE?

    While creating a new table with a primary key, it allows only one column to be selected from drop down list. How can I select multiple columns for a primary key?

    Thanks.

    DB:2.82:How To Create Multi-Column Primary Key Using 10g Xe? sz

    Thank you for your response.

    I found the way to do it.

    While creating primary key, there are 3 types to select. To create compound key, "Not Populated" type needs to be selected. Then, it will allow you to create compound key.

    Thanks.

  • RELEVANCY SCORE 2.81

    DB:2.81:Convert Relational Table To Dimension fx


    Hi,

    Suppose I have a Employee table includes columns:

    + emp_id (primary key)
    + emp_parent_key (a foreign key to emp_id)
    + emp_name
    + emp_birth

    Could anyone tell me how to create the hierachies and attributes for dimension Employee?

    Thanks,
    Truong

    DB:2.81:Convert Relational Table To Dimension fx

    Just a note on the CONNECT BY. Since the latest patch for Paris (10.2.0.2.8) OWB does support it.
    I have not tried this so, your mileage may vary.

    Ragnar

    From the Release Notes:

    5412790: TREE WALKING LOGIC IN WAREHOUSE BUILDER MAPPINGS
    Beginning in Warehouse Builder 10.2.0.2, the Mapping Editor enables you to recursively join a table onto itself.
    Also known as tree walking, recursively joining a table back onto itself enables you to retrieve records in a hierarchy. For example, consider a table that contains employee data including the manager for each employee. Using tree walking, you could determine the hierarchy of employees reporting up to a given manager.
    To perform tree walking in the Warehouse Builder, create a mapping and add the table that contains the hierarchal definition. Connect that table to a filter operator. In the filter operator, define the filter condition with CONNECT BY as the first two words. Include only the connect by logic in the filter operator. That is, do not include any AND or OR logic in the filter.

  • RELEVANCY SCORE 2.81

    DB:2.81:How To Create Fact Table Using Dimension Tables a9



    i have created QVD files ( Dimensions Tables )

    as i try to use to create fact tables so itself it creates synthetic key instead of mine

    what i want to do is

    1) dimension table User

    userid key

    name

    address

    2) dimension Table Target

    targetid key

    targetname

    area

    email

    3) fact table

    userid

    targetid

    how to achieve this

  • RELEVANCY SCORE 2.81

    DB:2.81:How To Create A Primary Key By 2 Columns In Sql?? 3z


    as title
    thx a lot.

    DB:2.81:How To Create A Primary Key By 2 Columns In Sql?? 3z

    Chris,

    Well you have two basic ways. One is to issue an alter table command and define a PRIMARY KEY (also called a PK). The other is to create a UNIQUE index. The PRIMARY KEY creates an index anyway, though I prefer to just use unique indexes as you can specify more options in this manner. A table can only have 1 PK where as you can have multiple UNIQUE indexes. I also do not combine the PK definition directly into the CREATE TABLE command, as normally one would keep a seperate PK script, even if they did contain only unique indexes commands and not PK alter table commands.

    Anyway, below are the two methods of making a PK/Unique object for your table. The table name used here is T1 ...

    Method #1 (Primary Key)

    ALTER TABLE T1 DROP PRIMARY KEY;
    ALTER TABLE T1 ADD PRIMARY KEY
    (Column_1,Column_2, ...)
    STORAGE
    (
    NEXT 512K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
    );

    Method #2 (preferred) : Unique Index

    DROP INDEX PK_T1;
    CREATE UNIQUE INDEX PK_T1 ON T1
    (Column_1,Column_2, ...)
    TABLESPACE INDX
    PCTFREE 10
    STORAGE
    (
    INITIAL 1M
    NEXT 512K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
    );

    It's always best to define the storage clause for both the create table and it's indexes.

    Hope this helps ya,
    Tyler

  • RELEVANCY SCORE 2.81

    DB:2.81:Multiple Joins From One Dimension Table To Single Fact Table j8


    Hi all,

    I have a single fact table with attributes as such:

    Action_ID
    Action_Type
    Date_Started
    Date_Completed

    and a Time Dimension to connect to the fact table. Currently the 2 Date columns in my fact table are in the format of 20090101 (which is the same as my key in the time dimension), if it means anything. I've tried to create multiple joins but have been getting error messages. What is the easiest way to link the time dimension with the two date columns in my fact table? Thanks.

    DB:2.81:Multiple Joins From One Dimension Table To Single Fact Table j8

    Kishore is right.

    Just to add an example because it's not really a between :
    http://obiee101.blogspot.com/2009/02/obiee-reporting-on-dateranges.html

    Good day everybody
    Nico

  • RELEVANCY SCORE 2.81

    DB:2.81:Create Dimension za


    At creation of levels of Dimension, for every level is the ID (for example table of TIME in which for a level a year is - Year_id, for a level Quarter - Quarter_id, for a level month - month_id ) utillized in a table, how to create Dimension, when we have 2 columns of ID and PARENT_ID only?????
    when the depth of hierarchy for us is not known???

    DB:2.81:Create Dimension za

    go to http://www.oracle.com/technology/products/bi/olap/index.html and there should be a download section on the right where you amongst other things will find analytic workspace manager.

  • RELEVANCY SCORE 2.81

    DB:2.81:Can We Create Partitioned Indexing On Null Columns-Performance Impact? mz


    Hi,
    I have very big tables in my datawarehouse.doing horizontal table partitioning to increase performance of our ETL.
    Now I have to select columns (key columns include columns) for non clustered indexing .
    Some columns have crores of null values but we are using them in our conditions like in where, join,gruopby/orderby,select clause.
    What will be the impact on performance on including those null values columns in indexing
    ( as key columns include columns).
    Please advise me on this.

    Kali charan tripathi
    tripathi_soft@yahoo.co.in
    INDIAKali Charan Tripathi New Delhi INDIA

    DB:2.81:Can We Create Partitioned Indexing On Null Columns-Performance Impact? mz

    Hi Shehap/Eileen,
    Thank you very much for your quick perfect response.
    Thanks
    Kali Charan Tripathi
    Kali Charan Tripathi New Delhi INDIA

  • RELEVANCY SCORE 2.80

    DB:2.80:Unicode Causes Dimension Build Failure? jk


    Dimension Full build fails with 2 error messages below. 
    Customer dimension LastName column populated with ALL Chinese unicode characters.
    Customer dimension FirstName column also populated with ALL Chinese unicode characters.
    Columns are not used as name property for the key column. 
     
    Columns have been demoted to member properties by turning AttributeHierarchyEnabled to false
     
    This occurs when final SELECT DISTINCT (all columns) statement is executed:
     
    Errors in the OLAP storage engine: The attribute key cannot be found: Table: dm_DimConsultant, Column: LastName, Value: 哈斯也提. . Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Consultant of Dimension: Consultant from Database: Intelligence_v2, Record: 567894.
        Errors in the OLAP storage engine: The attribute key cannot be found: Table: dm_DimConsultant, Column: LastName, Value: 哈斯也提. . Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Consultant of Dimension: Consultant from Database: Intelligence_v2, Record: 567894.
     
    The collation settings are as follows:
    Windows Collation: Latin1_General
    Language: English (United States)
     

    DB:2.80:Unicode Causes Dimension Build Failure? jk

    Using the Ignore Failures setting is the only way the dimension will build.
     
    After further review, the failing unicode string were traced back to the source SQL tables, where it was discovered that SQL Server was treating them as bad characters (displaying as ?).  It appears that the error is not in SSAS processing or collating, but the data itself was bad, and was unable to be processed by SSAS.

  • RELEVANCY SCORE 2.80

    DB:2.80:Calculation Column k3


    Hi All,
    I have oppty fact table and product dimension table.
    I have to create calculation columns
    As credit revenue ( sum( opptyfact. Revenue where product. productgroup = credit))
    Debit revenue (sum( opptyfact. Revenue where product. productgroup = debit))

    How can create this calculation column in meta using fact and dimension table. Please help . me
    Thank you for your time

    DB:2.80:Calculation Column k3

    Hi,

    In you oppty fact tbale LTS General Tab cick on "ADD" and select Product Dimension Table.Then create new column for Credit Revenue and Debit Revenue
    Double Click on OPPTY FACT TABLE LTSand in Column mapping select "SHOW UNMAPPED COLUMNS"

    Then in
    Credit Revenue
    case when product. productgroup = credit then opptyfact. Revenue end

    Debit Revenue
    case when product. productgroup = debit then opptyfact. Revenue end

    to select product. productgroup column click on Expression buider.

    and apply Aggregation as SUM on the both columns.

    Hope this helps.

  • RELEVANCY SCORE 2.80

    DB:2.80:Obiee 11g - No Fact Table Exists At The Requested Level Of Detail 97


    My dimesion tables are snow-flake.

    Table1 has Key, ProductName, ProductSize, Table2Key
    Table2 has Key, ProductDepartment, Table3Key
    Table3 has Key, ProductDivision

    I have created 2 hierarchies (in same dimension Product). Note: ProductSize is in Table1.

    ProductDivision ProductDepartment ProductName (shared level)
    ProductSize ProductName (shared level)

    There are 2 fact tables

    Fact1 is at ProductName level
    Fact2 is at ProductDepartment level

    When I create a request with columns as ProductSize and some measure; and filter it on ProductDepartment. The request fails with error "No fact table exists at the requested level of detail", but the request can ideally be answered using fact with ProductName level.

    I have properly defined logical level keys in the hierarchies and logical level in the LTS (content tab)

    Can anyone point me what I am doing wrong here?

    DB:2.80:Obiee 11g - No Fact Table Exists At The Requested Level Of Detail 97

    My dimesion tables are snow-flake.

    Table1 has Key, ProductName, ProductSize, Table2Key
    Table2 has Key, ProductDepartment, Table3Key
    Table3 has Key, ProductDivision

    I have created 2 hierarchies (in same dimension Product). Note: ProductSize is in Table1.

    ProductDivision ProductDepartment ProductName (shared level)
    ProductSize ProductName (shared level)

    There are 2 fact tables

    Fact1 is at ProductName level
    Fact2 is at ProductDepartment level

    When I create a request with columns as ProductSize and some measure; and filter it on ProductDepartment. The request fails with error "No fact table exists at the requested level of detail", but the request can ideally be answered using fact with ProductName level.

    I have properly defined logical level keys in the hierarchies and logical level in the LTS (content tab)

    Can anyone point me what I am doing wrong here?

  • RELEVANCY SCORE 2.80

    DB:2.80:Named Queries As Dimensions pd


    HiI have a dimension which i'll call Dimension 1. This dimension table joins to the fact table Budget IDFCT Budget ID. There is a primary-foriegn key relationship set up between the tables on the database.I have a need to replicate Dimension 1 and make some changes, i will call this Dimension 2. What i have done is copy the fact budget ID column in the DSV using a named query, so now i have 2 key columns for Budget.I have also used a named query to create the dimension input for dimension 2 by using the dimension 1 table in the named query.After all that i have in theory 2 dim tables and 2 fact table key columns.This all works fine until...... I use a scope statement on dimension 1 to set a default. Dimension 1 is all good but dimension 2 only has values for the defaults set in the scope statement. These are seperate dimensions but the statement seems to affect both.Does anyone have any idea why this might be happening? BTW, the IS LAST CYCLE IN YEAR attribute is not in Dimension 2. Cheers
    SCOPE([Dimension 1].[Budget Description].defaultmember);

    THIS = ([Dimension 1].[IS LAST CYCLE IN YEAR].[True]);

    END SCOPE;

    DB:2.80:Named Queries As Dimensions pd

    There's no way I can think of to change the default of one dimension without impact to all queries. If you had a three dimensional space, (x, y, z), there is no way to specify a coordinate in that space without specifying a member on the x, and the y, and the z axis. If you fix one on the coordinates, say z = 3, you've now effectively reduced the dimensionality of the space to a (x, y) plane passing through z = 3. SSAS works on those same mathematical principals.I'm not real clear on what you're trying to do and why so there's something I'm missing. You may look at a conditional assignment of the All member that depends on the CurrentMember of the [Budget Year] attribute hierarchy. Something maybe like the following and it may accomplish what you're trying to achieve.
    ([Dimension 1].[Budget Description].[All]) = IIf(
    [Dimension 1].[Budget Year].CurrentMember IS [Dimension 1].[Budget Year].].[All],
    [Dimension 1].[Is Last Cycle In Year].[All],
    [Dimension 1].[Is Last Cycle In Year].[True]
    );

  • RELEVANCY SCORE 2.80

    DB:2.80:Epmdimensionoverride xz


    Hi Guys/GalsI am working with EPM10 trying to do multiple Dimension Overrides on my Time Dimension which is part of my Column Key Range and contains approx 50 members which I need to be dynamic and driven from a member property. I have accomplished this outside of the Column key range and have the require member per column that I select using the EPMDimensionOverride function. I have managed to get it working for a some of columns as I found there is a 256 character limitation to the cell that the function exists in. When I create a second EPMDimensionOverride to cater for the rest of the columns it works fine but then after a refresh extra blank columns are inserted between the time members of the first EPMDimensionOverride. How do I stop this from happening???ThanksDev G

    DB:2.80:Epmdimensionoverride xz

    Hi Dev, Ok here is a way to do what you are trying, i didn't discover this, it was my mate who im working with.Forget aboutn the Formula EPM Override.1. in the Page Axis Write the time and the category exactly as you want to show it (you have to have the member recognition option activated). :Example:2010.12 2011.12 2012.12Actual Budget Budget2. Create a new report once you have this with Time and category in Columns and the Dimension you want to expand in rows. 3. The report will expand the time x and the category y selected from the context right?4. Here is the trick: Just one category is displayed since just one category is selected on the context, what you need to do is: - Select the first cell for time expantion, click on the "fx "button; you will see the parameters needed for the formula EPMOlapMember.... etc.... what you need to do with the first parameter Unique Name 1 is write: EPMMEMBERDESC( HERE do the reference to the cell in the Page axis, in this cas the cell with 2010.12). - Now do the same with the Category.And vual, you will have your expansion just as you defined it in the Page Axis.I hope this solve your problem.

  • RELEVANCY SCORE 2.80

    DB:2.80:Sap Business Objects Xi R3 Universe Designer : How To Use Case When Expression On Multiple Columns? sk



    Hi all,

    I am trying to create a Dimension object based on 2 columns of 2 tables. Here is the syntax, it should probably be more explicit than a long sentence :

    CASE

    WHEN "Table1"."Column1" = 'Value1' THEN 'Dimension Value1'

    WHEN "Table1"."Column1" = 'Value2' THEN 'Dimension Value2'

    WHEN "Table1"."Column1" 'Value1' AND "Table1"."Column1" 'Value2' AND "Table2"."Column2" NULL THEN "Table2"."Column2"

    WHEN "Table1"."Column1" 'Value1' AND "Table1"."Column1" 'Value2' AND "Table2"."Column2" = NULL THEN 'Autres'

    END

    The Parsing result for this expression is : Parse failed : Exception: DBD, The multi-part identifier "Table2"."Column2" could not be bound.State: S0001.

    I think this is because Case expression can not be used with 2 different columns but I can't find any other way to create my dimension.

    Would someone be able to help me on this issue?

    Best Regards.

    Yacine.

    DB:2.80:Sap Business Objects Xi R3 Universe Designer : How To Use Case When Expression On Multiple Columns? sk


    Hi Mani,

    Thank you very much for your help. It was very helpful! I had to add Table2 to the list of tables of my dimension object!

    Thank you very much!

  • RELEVANCY SCORE 2.79

    DB:2.79:How To Join Fact Table To Dimension Table Where The Column In Deminsion Table Is Not Primery Key. 38


    Hi,I am creating join between Fact and dimension table. But the column in dimension table which i am using in join is not a primary key. For Example: Row_Id and Enp_No are two columns in Dimension table. Enp_No is primary key in dimension table and Row_Id is normal column. Now according to my requirment, i have to join fact table to Row_Id of dimension table. when i am doing this i am not getting my dimension table in cube. In ssas selfjoin is not supported. Kindly suggest me how to achieve this.Thanks in advance:)

    DB:2.79:How To Join Fact Table To Dimension Table Where The Column In Deminsion Table Is Not Primery Key. 38

    Ans:We can join fact table column to non Primery key column of dimension tables. After defining relationship it will show key symbol for that particular column in dimension table. it will not creat any errors.Correct me pls If I am wrong:)

  • RELEVANCY SCORE 2.79

    DB:2.79:How To Create A Hierarchy On 3 Dimension Tables kj


    Hi,

    I have a one doubt on Hierarchy in 11g.
    I have 6 tables 5 dimensions and 1 Fact. The Fact contains all the 5 dimensional table Keys.
    And In every dimension i have only key and 1 column. Here the requirement is i have to drill the 1 dimension to another dimension columns.I need to do this for 3 levels(3 differant dimension columns)

    Thanks

    DB:2.79:How To Create A Hierarchy On 3 Dimension Tables kj

    Hi,

    If you need to navigate from one dimension hierarchy to other than you can use preferred drill path (you need to specify for each dimension column in the hierarchy).
    Or if you are looking for multiple hierarchies from one dim hierarchy then refer below-
    http://obieeexpress.blogspot.in/2011/05/one-dimension-multiple-hierarchies.html

    Sorry if my assumptions above is wrong.
    Hope this helped/ answered.

    Regards
    MuRam

  • RELEVANCY SCORE 2.79

    DB:2.79:Multi-Dimensional Forecasting With Time Series Sql 2008 sz


    Hello, In addition to time, I have 2 dimensions (keys) used to identify data in my model. I need to forecast based on all 3 of these dimensions using the Time Series algorithm in SQL Server 2k8. I am wondering how to model this. Naturally, I will use TIME as the Time Key and one of the other dimensions as the KEY column. Any advice on using the other dimension? Would I create a VALUE/PREDICT column for each item in that other dimension? There could be 100s of items: TIME  DIM1 DIM2_ITEM1 DIM2_ITEM2 DIM2_ITEM3 DIM2_ITEM4 ... OR, would I create a compound dimension for the KEY column, joining my 2 non-TIME dimensions: TIME DIM1-DIM2 VALUE Thanks in advance, and my apologies if my question seems to obtuse. I would be happy to clarify if needed. -- EngFM

    DB:2.79:Multi-Dimensional Forecasting With Time Series Sql 2008 sz

    You will need to create a compound key for all the non-time dimensions.   You can either architect your cube this way, or take the result of an MDX query and feed that into a mining model directly.-- Jamie MacLennan
    -- SQL Server Data Mining
    -- http://blogs.msdn.com/jamiemac

  • RELEVANCY SCORE 2.79

    DB:2.79:Filter Active Employee Working Day Using Time Dimension? c1


    I am working in SSAS 2005.My Requirement is very simple.Filter Active Employee WorkingDayCount using(DateofJoining and TerminationDate)  for any period of timeDimEmployee Table:        columns: EmpKey,EmpHireDate,EmpTerminationDate,EmpHireDateKey,EmpTerminationDateKey and DimDate Table :Columns:       DateKey,Date ,Year,Month,Day,BusinessDay,....Using these tables i have created DimDate,DimEmployee Dimension.1. If i select the some period in the DimDate.[Time] Hierarchy i want to filter employee those who are working in this period of time ?(Using HireDateKey, TerminationDateKey)2. Get the Count of each Employee WorkingDay in this period of time(Using HireDateKey, TerminationDateKey)How to achive these in SSAS? I am ready to do any work around to achive this(Create Named calculation or Set using MDX Query,SSAS Design,ETL Desing for Load Measure Value)any help is appreciated .ThanksLiyasker Samraj K

    DB:2.79:Filter Active Employee Working Day Using Time Dimension? c1

    Thank furmangg.It will work Out. If a make it as a FactTable then it is easy to handle. But I have put SCD Type 2 in my Employee Dimension(UserID Used as KeyColumn for SCD).  If i face any problem While doing this then i wl let U know.Thanks yor Your time.ThanksLiyasker Samraj K

  • RELEVANCY SCORE 2.79

    DB:2.79:Show/ Hide Expression 7c



    Hello,

    I am using QV11.

    I have 2 columns on the expressions side.

    For 1st column, I want to show or hide the it based on the dimension value.

    For 2nd column, I want the label or header of the column to be displayed based on the dimension value.

    Is it possible?

    DB:2.79:Show/ Hide Expression 7c


    Yes, it is possible.

    you can use getselections() functions..

    Please upload the sample app and explain your requirement so that we can help you out.

    HTH

    Sushil

  • RELEVANCY SCORE 2.79

    DB:2.79:Query On Index Key And Unique Key 8d


    I have a table with around 10 columns. This table has unique key created for three columns(Column1,Column2 Column3)

    First question :
    I will be using following statement.
    SELECT * FROM TABLE WHERE COLUMN1
    = 'XXX' ANDCOLUMN2 = 'YYY' ANDCOLUMN3 = 'ZZZ'
    In this case whether query performance will improve if I create a Index Key for these 3 columns or having unique key for these columns itself enough.
    Second Question:
    I also want to use following query
    SELECT * FROM TABLE WHERE COLUMN1
    = 'XXX' ANDCOLUMN2 = 'YYY'
    In this case, whether I need to create an Index Key for these two columns in order to improve performance or having Unique or Index key for the three columns itself will give the same improved performance?

  • RELEVANCY SCORE 2.79

    DB:2.79:Populating Fact Table From Dimension 37


    Hi,
    Here's my original data file:
    -----------------------------A flat file with  columns from a geographical hierarchy such as:
    Country  Zone  State   County City Store Sub Store , etc.
    The file also has data columns for months  to the right of the above columns such as:
    Jul  Aug Sept ......... basically 25 of these columns for two years' data for one product and another set of 25 columns for another kind of product. A typical record in the file looks like:
    Country  Zone        State   County              City       Store        Substore
    USA        Southeast  FL      Hillsborough    Tampa      walmart       Fletcher               
     
    May04_ProdA ....Jun06_prodA               May04_ProdB...Jun06ProdB  144                         160                                   180                        158
    I've uploaded this data into a staging table with identical columns,and have created a Geography(location) dimension table with an auto generated dimension key using Identity specification.
    I now need to populate my Fact table, the measures (basically the facts under the month columns and a few calucaltions for percentage , etc) need to go into  the Fact table. How do I populate my fact table? I need the autogenerated key from my Geography dimension table as an FK into my Fact table as well as the data for 50 months (grain of fact) and an aggregated measure (basically May04_prodA/May04_prodB , etc expressed as a %) ?
    I suppose i need to populate the fact table from the dimension using it as a lookup but I don't have any natural keys in the source file.
    Thanks.
     
     

    DB:2.79:Populating Fact Table From Dimension 37

    Hi,
    Here's my original data file:
    -----------------------------A flat file with  columns from a geographical hierarchy such as:
    Country  Zone  State   County City Store Sub Store , etc.
    The file also has data columns for months  to the right of the above columns such as:
    Jul  Aug Sept ......... basically 25 of these columns for two years' data for one product and another set of 25 columns for another kind of product. A typical record in the file looks like:
    Country  Zone        State   County              City       Store        Substore
    USA        Southeast  FL      Hillsborough    Tampa      walmart       Fletcher               
     
    May04_ProdA ....Jun06_prodA               May04_ProdB...Jun06ProdB  144                         160                                   180                        158
    I've uploaded this data into a staging table with identical columns,and have created a Geography(location) dimension table with an auto generated dimension key using Identity specification.
    I now need to populate my Fact table, the measures (basically the facts under the month columns and a few calucaltions for percentage , etc) need to go into  the Fact table. How do I populate my fact table? I need the autogenerated key from my Geography dimension table as an FK into my Fact table as well as the data for 50 months (grain of fact) and an aggregated measure (basically May04_prodA/May04_prodB , etc expressed as a %) ?
    I suppose i need to populate the fact table from the dimension using it as a lookup but I don't have any natural keys in the source file.
    Thanks.
     
     

  • RELEVANCY SCORE 2.79

    DB:2.79:Level Based Dimension 9m


    Hi All,

    I have requirement to implement level based hierarchy using the fields from three different dimensions. The three columns are in three different tables. On all the examples I have seen all the components/fields for level based hierarchy are from one single dimension table( like country, region, state,district from Region table).Is there a way to create level based hierarchy from different dimension tables.

    Thanks,

    Virat

    DB:2.79:Level Based Dimension 9m

    Set the content level with dimension

    Appreciate if you mark on helped suggestion so far ;)

  • RELEVANCY SCORE 2.78

    DB:2.78:Significance Of Dimension In Mpos 91



    Hi all,

    We can create MPOS using Tcode /SAPAPO/MSDP_ADMIN.

    Here we create POS using existing Characteristics.

    There is a field called "Dimension", we have to assign to each Characteristic.

    Usually the value for Dimension is 1.

    Qus :- (1) What is the significance of Dimension ?

    (2) What can be the possible values for Dimension ?

    (2) In which scenario its value can be 1 or 2 or etc. ?

    Thanks,

    Debi

  • RELEVANCY SCORE 2.78

    DB:2.78:How To Populate The Time Component Of A Cube? 97


    We have a question regarding how to populate the time component of a cube. Let me explain:

    We are using OWB 10gR2. We have created a cube with several dimensions. We are now building the mapping to load the cube. The cube operator has two columns for every dimension (e.g., "customer" and "customer_id" for the "customer" dimension).

    We understand that, in this case, "customer_id" stands for the dimension business key, so we create an arrow from the business key in the source table to the "customer_id" column in the cube operator.

    So far so good. The mapping works all right, and the cube is loaded correctly.

    Now we need to do the same for the time dimension. We have already created the time dimension and we have loaded it. We have also included it in the cube, so now we have two new columns in it: "time_day_code" and "time", both NUMBER data type.

    We have the "sale_date" column (DATE data type), in the source system and, of course, now we want to populate the date column in the cube. We suppose that, somehow, we have to translate the "sale_date" field into the numeric column of the surrogate key of the time dimension. How should do we do this? I suppose that OWB must do the translation for us, just as it does for the other dimensions, but how? We have been looking into the manuals, and we have found no explanation on how to go about this.

    Any help would be appreciated.

    Best regards

    Juan Algaba

    DB:2.78:How To Populate The Time Component Of A Cube? 97

    Yes, now I have done it, and it works all right.

    Thanks a lot

    Juan Algaba

  • RELEVANCY SCORE 2.78

    DB:2.78:Server Time Dimension And Rounded Datetime Using Amo 77


    Hi Everyone,

    I'm using AMO to dynamically create Cubes.
    I'm facing a little problem there :
    I'm creating a Server Time dimension, which works quite nicely but, my Data can't be bound to the right Member within my dimension because they don't match the member key.
    My Key column of the main dimension attribute is a TimeAttributeBinding withOleDb.OleDbType.Date
    as type (which maps a System.DateTime)
    This mean that my member keys looks like : [2011-11-24T00:00:00]
    But I have Data Like '2011-11-24 13:37:42'
    So when I process the cube,'2011-11-24 13:37:42' is not found in the Date Dimension so it's putting everything into the UnknownMember

    In order to avoid that, I have 2 solutions to do on my Fact Table :
    - Change the type Column to a type Date loosing the Time then
    - Transform each Value in order to just keep the Date and set 00:00:00 as the time

    But my question is : Is there any option / setting that let the cube does that for me (within the Server Time Dimension Attribute) ?
    Can I create a Server Time Dimension with members like [2011-11-24T00:00:00] and give data like '2011-11-24 13:37:42' and when the cube will process the dimension, it will just don't care about the Time in my DateTime column ?

    Thanks in advance,
    Regards,

    Gal

    DB:2.78:Server Time Dimension And Rounded Datetime Using Amo 77

    Ok.

    Thanks you both.
    Now that I know that it isn't possible, I've changed my DataSourceView and added ComputedColumn that will have the result of the TRIM.

    Regards,

  • RELEVANCY SCORE 2.78

    DB:2.78:Automated Duplication For Role Playing Dimensions 8d


    Im wondering if there is way to automate (short cut method) to duplicate the entire Dimension.
    Simply copying and pasting or Duplicating will not create the Hierarchies.

    For Example:
    I have Fact_Transaction (with columns Trx_Id, Trx_Date, Create_Date, Update_Date, Invoice_Date, Amount)

    I have a date dimension joined to this fact in physical layer on the Trx_Date.

    Problem:
    Now if I get a requirement to show the other dates 2 as dimension,
    I need to create alias for the same time dim and manually create the dimensions and hierarchies in the BMM layer.

    Is there any much better way we can do this using some scripts (xudml, /commands, ..etc)

    Thanks
    Kaushik

  • RELEVANCY SCORE 2.77

    DB:2.77:Sub Columns In Pivote Table jz



    Hello,

    I want to create sub colums in Pivot table.

    I know we need to drag 1 dimension over the expressions and create the subcolumns for all the columns in expressions.

    But I dont want in this way.

    I have 5 columns, out of which for 2 columns i need 1 sub column and for other 2 columns diff sub column and 1 column without any sub column.

    I would like to know is it possible? If yes how? and if not any work around for the same?

    Below is the format I need.

    DB:2.77:Sub Columns In Pivote Table jz


    sorry missed the image.

    Please check the attached image.

  • RELEVANCY SCORE 2.77

    DB:2.77:The Level Relation And Level Order Valueset Provide Inconsistent Level Map d7


    There are 3 dimensional tables in database in snowflake schema.
    - level1
    --- level2
    ------ level3
    All tables linked using referential integrity constraints to the parent primary key.

    In AWM there is a dimension with 3 levels and tables are mapped to the dimension.
    Dimension use natural keys from database and marked as snowflake.
    Levels are ordered and all member, parent attributes mapped to appropriate columns (columns are under ref. integrity constraints)

    When trying to maintain the dimension AWM throws exception:
    "The level relation and level order valueset provide inconsistent level mappings"

    What could be the reason for the exception?

    DB:2.77:The Level Relation And Level Order Valueset Provide Inconsistent Level Map d7

    There is a block above the first post "This question is not answered. Helpful answers available: 5. Correct answers available: 1"
    and no hyperlinks ... where can I get that "helpfull" and "correct" answers???!!!

  • RELEVANCY SCORE 2.77

    DB:2.77:Comparision Issue In Scd Ii kz



    Hi Legends,

    I have a situation with scd type 2, My dimension contain 65 columns in which around 45 columns are updatable.

    I give look up condition on key columns and forward all updatable records to expr transformation,

    So, Here the issue is....

    Is it mandatory to compare all 45 columns from lkp and src in my expression transformation to let know about updated records(for applying flag).

    I think comparing 45 columns using IIF in expr transformation is a performance issue.

    Both src and tgt contain huge amount of data.

    Please let me know if any alternative approaches

    for 'flat file src and relational tgt' and also for 'relational src and relational targets' as well.

    Thanks inadvance.

    Chandu...!

  • RELEVANCY SCORE 2.77

    DB:2.77:Select A Non Key Granularity Attribute Issue Ssas 3j


    Hi Friends,
    I have small issue
    in the Customer Dimension I have two primary key columns(surrogate keys) 1.CustID 2.CustRegID
    I was making relations with other two fact tables SalesCust_Fact(CustID(Foreign Key)) and
    Reg_Fact(CustRedID(foreign key))
    by using Customer Dimension
    So when I was making relations in the Dimension usage between Reg_Fact and
    Customer Dim based on CustRegID
    it is claiming like this
    if you select a non-key granularity, the server will not be able to aggregate data properly unless you make sure that all other attributes are directly or indirectly related to it by specifying them as related attributes

    The data for Reg_Fact is displaying repeated data.
    Can anyone tell me where I need to do changes please?
    Thanks,
    RK

    DB:2.77:Select A Non Key Granularity Attribute Issue Ssas 3j

    This is probably more than a warning. This is probably an indicator of a design flaw. A table can have one and only one primary key though it can have an Alternate or unique constraint. I've never seen a case nor can I imagine a case where two surrogate
    keys would be needed.
    If you have both measure groups relating to different attributes in the dimension and only one can be a Key attribute and everything else must relate to the key, you better have your dimension structured so that one surrogate is the Key, and every other
    attribute defined in your dimension is directly or indirectly related to the second surrogate key attribute (The one not defined as a key). At least in that way every other attribute would still be available for analysis. Your natural hierarchies should look
    something like the following:
    ----
    Attribute --- Parent Attribute
    Key Attribute -- Other Key Attribute

    ----- Another Attribute --- Another Parent Attribute
    There are cases where facts relate to different attributes that are valid such as when one business process is at the day grain and another at the month grain. For the metrics at the Month grain, if the Day attribute is included in the query, you'll get
    the month values repeated by the number of days in the month.
    HTH, Martinhttp://martinmason.wordpress.com

  • RELEVANCY SCORE 2.77

    DB:2.77:Kfs In Bpc d3



    Hi Folks:

    As you know, the objects configured in BPC automatically generate Objects in BW.

    (e.g.) Master Data:

    1.- Dimension Dimension Members in BPC Characteristics Values in BW (Ok)

    My cuestion is in case of Transaccional Data:

    2.- No create any Object in BPC for Transaccional Data Automatically get an Key Figure in BW (??)

    Questions:

    1.- Why only One Key Figure in BW for BPC? (Normally in BW we manage many Characteristics many KFs)

    2.- BPC are limited to one Object for Key Figures?

    3.- How manage many Key Figures BPC, using only one KFigure Object in BW?

    Thanks a lot

    DB:2.77:Kfs In Bpc d3


    Hi Noe-

    Spot on about the Account Base model concept!

    Sheldon

  • RELEVANCY SCORE 2.77

    DB:2.77:Populating A Dimesion Table f8


    I am new to Data Integrator (having previously used Oracle Warehouse Builder).

    Can any one provide simple examples/tutorials for how to populate a dimension (not SCD)? For example I want to create a dimension with 2 levels (expense category and expense type) based on a single datasource (with 2 columns expense_category and expense_type). There are a number of expense types per category:

    Categorytype
    MealsBreakfast
    MealsLunch
    MealsDinner
    TravelTrain
    TravelTaxi

    The resulting dimension should include surrogate keys plus descriptions and look (something) like

    Dim_keycategory_idcategory_nametype_idtype_name
    11Meals1Breakfast
    21Meals2Lunch
    31Meals3Dinner
    42Travel4Train
    52Travel5Taxi

    I can figure out how to do this using a multi-stage approach, first loading the categories into a separate 'category' table (and creating new category_id's when required) and then loading the dimension based on this table plus the source data. However, I can't help wondering whether there is a simpler way of doing this.

    Thanks,

    Chris

    DB:2.77:Populating A Dimesion Table f8

    Chris,

    I have been working with Sunopsis/ODI there are almost 5 years and this forum is the best place that I ever found..

    Maybe someone else know other places or means...

    Good Luck!

  • RELEVANCY SCORE 2.77

    DB:2.77:Dimension Table Indexes k8


    All dimension table, non-key columns
    should have individual bitmap indexes.

    All fact table, foreign key columns must
    have individual bitmap indexes on them.

    By doing the above will the performance for joins be better.?

    Rakesh

    DB:2.77:Dimension Table Indexes k8

    Well do remember bitmap indexes are best on columns where there not so many unique values. Everyone has differing opinions on what that # ought to be. In other words, the cardinality, or # of unique elements is not extremely high.

    IN general, bitmap indexes will improve performance given that the CBO is using them.
    Look at this post, it is a pretty good discovery on this sort of thing. it tends to be a design process, where you test with your given dataset and optimize it.

    http://www.rittmanmead.com/2007/07/27/playing-around-with-star-transformations-and-bitmap-indexes/

    -Greg

  • RELEVANCY SCORE 2.76

    DB:2.76:How To Filter A Dimension With A Parent-Child Hierarchy? 87


    I havea dimension with a parent-chid hierarchydefined on it. What I want is to show members of some level with some filtration (it can be both for upper and lower levels).
    I thought of something like

    select
    [cost] on columns
    , Categories.[Parent Key].levels(2).members on rows
    from
    [ax ctl]
    where (
    filter (Categories.[Key].members, ...)
    )

    DB:2.76:How To Filter A Dimension With A Parent-Child Hierarchy? 87

    Ah, I get you, thanks. You are using the same hierarchy and I believe it works. I've been trying to use different - the key and the parent-child.

    select
    cost on 0
    , exists(Categories.[Parent Key].levels(2).members, { Categories.[Key].[38] }) on 1
    from
    [ax ctl]

  • RELEVANCY SCORE 2.76

    DB:2.76:Multiple Foreign Key Joins Between Two Tables fx


    Hi,

    I have a question about building a repository.

    I have a date dimension and one fact. The fact table has about 10 foreign key columns that link to the date Dimension.

    In this case should I create 10 aliases to create joins in the Physical and BMM layer or is there any other way to handle this situation.

    I am asking this question because 10 aliases can get very confusing for me at the later point of time while creating reports.

    Using OBIEE 10.1.3

    DB:2.76:Multiple Foreign Key Joins Between Two Tables fx

    Gotcha...

    That helps me in understanding about this concept...

    I should e creating 10 aliases now...that is going to fun....

    Thanks again Veeravalli...

  • RELEVANCY SCORE 2.76

    DB:2.76:Which Is Better? Dimensions Or Attributes ( In This Case) 7s


    say i have a table CustDetailsDimensionTable. This table contains custName, CustAddress, CustPhone, CustEmail. I want to be able to browse based on each of these columns. should i :
     
    1) Create one dimension, on CustName For example, and use the rest of the columns as attributes
                                                                                      or
    2) Create a dimension for each column i want to be able to browse upon?

  • RELEVANCY SCORE 2.76

    DB:2.76:Logical Dimension Table kk


    Hi All,
    I am creating logical columns using two logical dimensions in a separate logical table. Do I need create complex joins for the new logical table, two source logical dimension tables have complex joins created already. Could you anyone provide some clarification on this.

    Thanks in advance,
    RK

    DB:2.76:Logical Dimension Table kk

    As mma1709 mentioned,
    you must be careful with the joins of this new logical table with your fact...
    When you check for errors in the administrators,are there any??
    if no,at any report with this column you have any problems??

    hope i helped....

    ///////////////////////////////////////
    http://greekoraclebi.blogspot.com/
    ///////////////////////////////////////

  • RELEVANCY SCORE 2.76

    DB:2.76:Slowly Changing Dimension Problem a8


    Hello,

    I am trying to turn a simple EmployeeDim dimension into a Slowly Changing Dimension (SCD), but deployment always fails

    Here's what I did:

    -
    Created a surrogate key (employee key) which is the PK for the Dim

    -
    Kept the relational DB PK as Business Key / Alternate Key

    -
    Added a Current column to DWH (and DSV)

    -
    For SCD type 2 attributes I am trying to create the following attribute relationship: Surrogate Key one-to-many Business Key one-to-many SCD Type 2 Attribute

    Deployments start failing once I change the attribute relationships as described above.

    I believe it got something to do with setting the Key Column for the SCD Type 2 Attribute. I tried using the Surrogate Key, the Business Key and a combined key (collection) with SCD Type 2 Attribute
    itself none works.

    I must say I ctually dont fully understand how the dimension should work. I understand that the Surrogate Key's
    AttributeHierarchyVisibleState
    should be
    False, so the cube be browsed via the Business Key, bt I dont understand
    how SSAS will know which is the Current member. Anyway since I cannot build the cube i
    cannot test it yet

    Many thanks!

    rn

    DB:2.76:Slowly Changing Dimension Problem a8

    Mostly you get the problems because of keys here and next thing is interpretation of data from dimension. So i thought it is best to share some examples and see if it could solve your problem.
    You may want to read this article.
    http://www.beeii.com/?p=371
    vinu

  • RELEVANCY SCORE 2.76

    DB:2.76:Join 2 Fact Tables kf


    I have 2 fact tables and 1 dimension table, which is joined with both these tables through primary key (ID). I want to build a report, which consists of some columns from these fact tables with this condition:

    FACT1.DIM_ID = FACT2.DIM_ID
    AND
    FACT1.DATE = FACT2.DATE

    DIM_ID is FK for dimension
    Should i create a bridge table?
    What is the best way to do this, please? Thanks....

    DB:2.76:Join 2 Fact Tables kf

    Thanks for reply,
    no...DATE is not a key, but this condition is necessary to join because of M:N relationship between these fact tables

  • RELEVANCY SCORE 2.76

    DB:2.76:Creating Description Dimension Off Of Key Values Off Of Fact Table. 7j


     
    Hi Everyone,
    I have a DSV on top of a fact table and some columns are just status values for example column Sales only has S and N for Sale and No Sale.
     
    I wanted to create a dimension to expand on the S and N, so that it is more user friendly. I was thinking of 2 options
    1) do a named query on top of the fact table or
    2) create a new table with surrogate keys and update fact table column Sales to use the surrogate key from this fact.
     
    I just wanted peoples opinion or any suggestions.
     
    Thanks in advance.

    DB:2.76:Creating Description Dimension Off Of Key Values Off Of Fact Table. 7j

     
    Hi Everyone,
    I have a DSV on top of a fact table and some columns are just status values for example column Sales only has S and N for Sale and No Sale.
     
    I wanted to create a dimension to expand on the S and N, so that it is more user friendly. I was thinking of 2 options
    1) do a named query on top of the fact table or
    2) create a new table with surrogate keys and update fact table column Sales to use the surrogate key from this fact.
     
    I just wanted peoples opinion or any suggestions.
     
    Thanks in advance.

  • RELEVANCY SCORE 2.76

    DB:2.76:Is Their A Limitation On How Many Dimnension I Can Create In Ssas 2008 3p


    Is their a limitation on how many dimension's can i create in SSAS. I have a dimension table with 255 columns( which are basically degenerate dimension) , I have a dimension in SQL server database engine Which has fk constraints to fact table on each colum to dimension , which relates to indivual fk key to primary key in dimension. this is resulting the cube to create 255 dimension. 1) How can i avoid creating 255 dimension and just create one junk dimension , without altering sql server dimension ?2) What are the limitation of Dimension in SSAS 2008?please advise thanks Option 1 is preference , because i want to treat that as a junk dimension, that would just treat it as an attribute and not a seperate dimension. I thought of manully creating in using Dimension desginer, but to sort that out of 1024 colums kind of make in little painful. any help in this regard is apprecaited.

    DB:2.76:Is Their A Limitation On How Many Dimnension I Can Create In Ssas 2008 3p

    I have two fact table with 4 confrmed dimension. The fact tables contains 1024 colums, the fact table contains reading information for equipments and some of the those information contains varchar columns. since i cannot represent the varchar columns as fact in measuregroup. I had created a junk dimension that would include all the varchar degenerate columns into a single junk dimension. Those 255 degenerate columns are now stored as a flat tableas a junk dimension. So now all the fk constraints are linked through the ID(Identity) column inJunk dimension to the relavent fkconstraint to the fact table. So all the columns have relationship.Not realized that this would become as role playing dimension and create 255 dimensionsfora single dimension in the cube.rather than representing255 dimension , i want to just represent the junk dimension asone dimension and not a role playing for all the 255dimension , which are nothing but attributes of junk dimension.each dimension would probably represnt just the ID column and the attribute column.please let me know if this helps

  • RELEVANCY SCORE 2.76

    DB:2.76:Level Based Hierarchy Problem jj


    Hi,
    I want to create time hierarchy on Business Layer. I created source table is contains MonthKey, Year, Half of Year, Quarter,Month columns.
    I created hierarchy level and I selected level key for all level. I want to Identify the Primary Key for a Dimension Level, I can not select Primary Key Area. becuase primary key area is blank.
    Why primary key is blank? What is wrong?

    Thank you replies...

    DB:2.76:Level Based Hierarchy Problem jj

    It depends on the joins defined in physical layer. if your primary key was not used for physical layer joins, then key may not be created.

    If it is time dimension, set chronological key, and define unique column as key.

    hope this helps..