Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Unanswered: OLAP - Dimension

    Hi Folks,

    We have problems with our Cube.
    One of our dimension has more 64K (64,000) members.

    We had tried to use grouping but it does not meet our needs.
    Can somebody suggest another way to to resolve this limitation problem besides grouping.
    Any comments/suggestion (even redesigning the dimension or fact tables) will be welcome.

    Thanks and have a great day.

  2. #2
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    This is a big question, can you post the DDL of the mart/warehouse that includes all dim tables and the fact table? If you are storing measures at the dim level you might be able to store them in the fact.
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  3. #3
    Join Date
    Nov 2002
    Posts
    6

    Re: OLAP - Dimension

    Hi There,
    i have this strong feeling that you can solve this problme by createing a groups on top of your dimension, but one thing every time you need to rebuild this dimension rather then just by increnmental update, rebuild it every time when you process the cube. becuase i have also got the same problem and i am doing the same.

    Thanks
    Pawan
    Originally posted by REVI
    Hi Folks,

    We have problems with our Cube.
    One of our dimension has more 64K (64,000) members.

    We had tried to use grouping but it does not meet our needs.
    Can somebody suggest another way to to resolve this limitation problem besides grouping.
    Any comments/suggestion (even redesigning the dimension or fact tables) will be welcome.

    Thanks and have a great day.

  4. #4
    Join Date
    Sep 2003
    Posts
    6
    Hi,

    This is the DDL:

    CREATE TABLE [dbo].[Dim_FII_Code] (
    [FII_Code_SID] [int] NOT NULL ,
    [FII_Code_Value] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FII_Code_Description] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FII_Code_Group] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Date_Created] [datetime] NULL ,
    [Date_Updated] [datetime] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Dim_Account] (
    [Account_SID] [int] NOT NULL ,
    [Account_Code] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_Industry_Code] [nchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_Industry_Desc] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_Start_Date] [datetime] NULL ,
    [Account_Term_Date] [datetime] NULL ,
    [Account_Country_Code] [nchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_Country_Desc] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_State_Code] [nchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_State_Desc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_City] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_Zip_Code] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DUNS_Number] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HQ_DUNS_Number] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Domestic_Ultimate_DUNS_Number] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Global_Ultimate_DUNS_Number] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Fact_ID] [int] NULL ,
    [Fact_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SIC_Code] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Global_4000_Company_Flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_Type_Group] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_Type] [nchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Sales_Region_Code] [nchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Sales_Territory_Code] [nchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Service_Code] [nchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Service_Desc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Date_Created] [datetime] NULL ,
    [Date_Updated] [datetime] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Dim_Date] (
    [Date_SID] [int] NOT NULL ,
    [Month_SID] [int] NOT NULL ,
    [Day] [datetime] NULL ,
    [Day_of_Week] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Weekday_Flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Date_Created] [datetime] NULL ,
    [Date_Updated] [datetime] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Fact_Search_FII_Code_Usage] (
    [Account_SID] [int] NOT NULL ,
    [Date_SID] [int] NOT NULL ,
    [FII_Code_SID] [int] NOT NULL ,
    [Total_Search_Qty] [int] NULL ,
    [No_Headline_Search_Qty] [int] NULL ,
    [Date_Created] [datetime] NULL ,
    [Date_Updated] [datetime] NULL
    ) ON [PRIMARY]
    GO


    We countering problems with Dim_FII_Code, the members for the
    exceed for the fii_code_group. It is more than 64k.

    Another thing, can you explain how grouping work.
    One data model without grouping and with grouping will help.

    Thanks and have a nice day.

  5. #5
    Join Date
    Sep 2003
    Posts
    6

    Re: OLAP - Dimension

    Originally posted by pawan
    Hi There,
    i have this strong feeling that you can solve this problme by createing a groups on top of your dimension, but one thing every time you need to rebuild this dimension rather then just by increnmental update, rebuild it every time when you process the cube. becuase i have also got the same problem and i am doing the same.

    Thanks
    Pawan

    HI ,

    Can you explain how grouping work.
    One data model without grouping and with grouping will help.

    Thanks and have a nice day.

  6. #6
    Join Date
    Nov 2002
    Posts
    6
    Hi There,
    what you can do that, create a Hierarchical dimension like Data_created and under that the sub member is your 64k dimension which is Dim_FII_Code. one thing don't take the full date just extracte year out of that date field and create one more field in your table called "Dim_FII_Code" i assume so and update that field with this year value. Then go back to your cube and create a Hierarchical dim as YEAR
    by using newly created field which is having year value and sub member is Dim_FII_Code. But make sure that this field is not null.
    if you have any ? then do mail to this address.
    pawan.bhatnagar@aut.ac.nz

    Thanks
    Pawan

    [
    Originally posted by REVI
    Hi,

    This is the DDL:

    CREATE TABLE [dbo].[Dim_FII_Code] (
    [FII_Code_SID] [int] NOT NULL ,
    [FII_Code_Value] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FII_Code_Description] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FII_Code_Group] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Date_Created] [datetime] NULL ,
    [Date_Updated] [datetime] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Dim_Account] (
    [Account_SID] [int] NOT NULL ,
    [Account_Code] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_Industry_Code] [nchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_Industry_Desc] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_Start_Date] [datetime] NULL ,
    [Account_Term_Date] [datetime] NULL ,
    [Account_Country_Code] [nchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_Country_Desc] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_State_Code] [nchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_State_Desc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_City] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_Zip_Code] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DUNS_Number] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HQ_DUNS_Number] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Domestic_Ultimate_DUNS_Number] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Global_Ultimate_DUNS_Number] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Fact_ID] [int] NULL ,
    [Fact_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SIC_Code] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Global_4000_Company_Flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_Type_Group] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Account_Type] [nchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Sales_Region_Code] [nchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Sales_Territory_Code] [nchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Service_Code] [nchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Service_Desc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Date_Created] [datetime] NULL ,
    [Date_Updated] [datetime] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Dim_Date] (
    [Date_SID] [int] NOT NULL ,
    [Month_SID] [int] NOT NULL ,
    [Day] [datetime] NULL ,
    [Day_of_Week] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Weekday_Flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Date_Created] [datetime] NULL ,
    [Date_Updated] [datetime] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Fact_Search_FII_Code_Usage] (
    [Account_SID] [int] NOT NULL ,
    [Date_SID] [int] NOT NULL ,
    [FII_Code_SID] [int] NOT NULL ,
    [Total_Search_Qty] [int] NULL ,
    [No_Headline_Search_Qty] [int] NULL ,
    [Date_Created] [datetime] NULL ,
    [Date_Updated] [datetime] NULL
    ) ON [PRIMARY]
    GO


    We countering problems with Dim_FII_Code, the members for the
    exceed for the fii_code_group. It is more than 64k.

    Another thing, can you explain how grouping work.
    One data model without grouping and with grouping will help.

    Thanks and have a nice day.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •