October 21, 2010 at 12:48 pm
I have a dimension table for our customers It's a type 1 dimension presently, eventually I plan to completely re-do it so it can support type 2.
CREATE TABLE [dim_Agencies](
[ai_agn_seq] [int] NOT NULL,
[ai_cust_id] [varchar](7) NULL,
[region] [varchar](30) NULL,
[terrtitory] [varchar](35) NULL,
[account_type] [varchar](9) NULL,
[user_count] [smallint] NULL,
[account_status] [varchar](35) NULL,
[agn_name] [varchar](35) NULL,
[conglomerate_name] [varchar](35) NULL,
[agn_alert] [varchar](3) NULL,
[agn_watch] [varchar](3) NULL,
[main_product_code] [varchar](4) NULL,
[main_product_desc] [varchar](35) NULL,
[main_product_family_code] [varchar](4) NULL,
[main_product_family] [varchar](35) NULL,
[main_product_version] [varchar](8) NULL,
[Product_Basefee] [numeric](10, 2) NULL,
[Initiation_date] [datetime] NULL,
[Expiration_date] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[ai_agn_seq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
My problem is with the dimension attribute user count.
I have it in my dimension design with the key set to the table's primary key (because if I set it to use the field as the key value I get a duplicate values error when I try to process the cube, because of course several customers have the same number of licensed users).
I have the name of user_count set to be the user_count field (type wchar because AS won't let me use Integer/SmallInteger).
How do I get it to sort the values in the numeric value of user_count when I drag the dimension into excel?
October 22, 2010 at 1:34 am
You should be able to add user_count as integer in SSAS. Let me know the error. what is the data type of user_count in DSV?
Thanks,
Amit G
http://www.msbiconcepts.com
October 22, 2010 at 6:57 am
In the DSV user_count has a datatype of smallint System.Int16
When I change the DataType on the User Count attribute NameColumn property from Wchar to SmallInt I get a red squiggly line underneath the attritube name.
Hovering a mouse over it I get
"The 'SmallInt' datatype is nto allowed for the 'NameColumn' property; 'Wchar' should be used.
If I save the dimension with the error, then the cube project won't build.
Error1DimensionAttribute [Agencies].[User Count] : The 'SmallInt' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.00
October 22, 2010 at 7:38 am
The KeyColumns has to be set to a composite value of user_count and the PK column
Then you can set the OrderBy property to Key and that should sort the data as required.
HTH
Naveen
October 22, 2010 at 8:26 am
Mark,
What version SSAS are you using? I ran up a quick test in 2008R2 and also 2005 and can't get either to throw the errors you've mentioned.
Below is the test data I used (note the dim table is your code, unchanged).
CREATE TABLE [dim_Agencies](
[ai_agn_seq] [int] NOT NULL,
[ai_cust_id] [varchar](7) NULL,
[region] [varchar](30) NULL,
[terrtitory] [varchar](35) NULL,
[account_type] [varchar](9) NULL,
[user_count] [smallint] NULL,
[account_status] [varchar](35) NULL,
[agn_name] [varchar](35) NULL,
[conglomerate_name] [varchar](35) NULL,
[agn_alert] [varchar](3) NULL,
[agn_watch] [varchar](3) NULL,
[main_product_code] [varchar](4) NULL,
[main_product_desc] [varchar](35) NULL,
[main_product_family_code] [varchar](4) NULL,
[main_product_family] [varchar](35) NULL,
[main_product_version] [varchar](8) NULL,
[Product_Basefee] [numeric](10, 2) NULL,
[Initiation_date] [datetime] NULL,
[Expiration_date] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[ai_agn_seq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO dim_Agencies (ai_agn_seq, region, user_count) VALUES (40, 'north', 7)
INSERT INTO dim_Agencies (ai_agn_seq, region, user_count) VALUES (20, 'south', 8)
INSERT INTO dim_Agencies (ai_agn_seq, region, user_count) VALUES (35, 'east', 6)
INSERT INTO dim_Agencies (ai_agn_seq, region, user_count) VALUES (41, 'north2', 7)
INSERT INTO dim_Agencies (ai_agn_seq, region, user_count) VALUES (21, 'south2', 8)
INSERT INTO dim_Agencies (ai_agn_seq, region, user_count) VALUES (36, 'east2', 6)
CREATE TABLE little_fact (ai_agn_seq int not null, somefact int not null)
INSERT INTO little_fact VALUES (40, 500)
INSERT INTO little_fact VALUES (41, 400)
INSERT INTO little_fact VALUES (20, 600)
INSERT INTO little_fact VALUES (21, 700)
INSERT INTO little_fact VALUES (35, 800)
INSERT INTO little_fact VALUES (36, 900)
Steve.
October 22, 2010 at 9:03 am
2008R2 RTM
If you set the type property of the Name column of a dimension to integer you don't get a big red error?
http://www.cryptoknight.org/img/SSAS_Dimension_Error.jpg
Blah my website is misbehaving... :/
October 22, 2010 at 10:35 am
Ok, on my local 2008R2 Developer (so Enterprise), I hadn't actually set a Name for the attribute (lazy but hey, it's a test). But when I did, I selected the user_count (which is apparently int16) and it changed it for me to a WCHAR. ie when i hover over the name column properties, it shows it as wchar but i definitely didn't set it to be that (if you couldn't tell, i am taking the "least mouse clicks to test something" route).
Steve.
October 22, 2010 at 10:36 am
and to answer the question - no, the only squiggly i get is a blue one, on the dim root, telling me i should do the right thing and create attribute hierarchies.
Steve.
October 22, 2010 at 12:37 pm
right but now that the name is wchar... take your sample cube and pull it into excel.
Try to sort the user count.
Now imagine several records where they are the same and you want to use excel to create a custom grouping to take agencies with user counts between 1 and 20, 20 and 50, 50 to 100 and 100+
considering they get sorted
1
10
100
101
11
2
20
21
22
etc
October 22, 2010 at 12:40 pm
What happens when you try what I had suggested?
October 22, 2010 at 12:55 pm
Do you want to be able to modify the sort, or have it come out specifically in (say) ASC order? If it's the latter, then why not set the sort attribute to be the key? You don't need the key (for the attribute) to be the unique Dim key, you should be fine using the user_count itself as the key. Not sure why you got that error on multiple values, perhaps an issue with attribute relationships?
Steve.
October 22, 2010 at 1:35 pm
naveendas (10/22/2010)
What happens when you try what I had suggested?
I set the key to be a composite of the
ai_agn_seq (PK) and the user_count.
I set the order by user_count.
Excel still sorts it alphanumerically when I bring the user_count into the workspace.
1
1
1
1
10
10
10
11
2
20
21
October 22, 2010 at 1:41 pm
stevefromOZ (10/22/2010)
Do you want to be able to modify the sort, or have it come out specifically in (say) ASC order? If it's the latter, then why not set the sort attribute to be the key? You don't need the key (for the attribute) to be the unique Dim key, you should be fine using the user_count itself as the key. Not sure why you got that error on multiple values, perhaps an issue with attribute relationships?
I'd like the sort to come out in ASC order. No reason to modify it. Users will likely do some customer grouping on it in excel, but that's not an issue for me.
If I set the key to just be the user_count the cube won't process this dimension.
I have the following for Attribute relationships
ai_agn_seq determines
account_type,
agn_name
expiration_date
initiation_date
main_product_code
main_product_desc
main_product_family
main_product_version
agn_name determines the rest.
My relationships are all set to Flexible
Cardinality 1
I did most of this so that the various attributes determined by the agn_name column will display when a mouse is hovered over them in excel, and be available without having to be stuffed into a hierarchy.
October 22, 2010 at 1:49 pm
set the key as user_count, PK (in that order). Then if you sort by key, it should sort by user_count and then by key. So user_count=2 will show up before user_count=10
October 22, 2010 at 1:53 pm
naveendas (10/22/2010)
set the key as user_count, PK (in that order). Then if you sort by key, it should sort by user_count and then by key. So user_count=2 will show up before user_count=10
That's what I did...
in order from top to bottom
user_count
ai_agn_seq (PK)
It doesn't sort that way... it still sorts by name.
Should I remove the name property column, and just have the keys? What will it do with a composite key in that case?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply