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.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.|||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.|||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.|||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.|||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.sql