Numeric column names are pre-pended with "ID" string

Hello

I'm using SQL Server 2005 Business Intelligence Studio and have noticed an odd behavior with column names.

Specifically, if you query for a table that returns a numeric column name it is pre-pended with "ID".

For example, if my databaseReporting Services will show these fields in the "Report Datasets" as follows:

For example,

"Select identity as '1' from group_header, 1" shows a column name of "ID1" instead of "1".

Has anyone else run into this? If so, is there a way to remove the "ID".

Jay

Fields names in RDL must be CLS-compliant. I.e. they have to start with a character. If the query defines a field name so that it is not CLS-compliant, report designer will automatically generate a unique, CLS-compliant field name, such as ID1.

Note: the field name has nothing to do with the actual field values returned by the query.

-- Robert

Numeric attribute keys

Does it make sense to favor numeric columns for the attribute keys (e.g. 1,2,3, etc. for the month attribute) as opposed to names (e.g. January, February). I've noticed that the sample AdventureWorks UDM uses both approaches so I am not sure which one is best from a performance standpoint. In AdventureWorks, numeric columns are typically used for snowflake dimensions while name columns are used in star schemas (except Date dimension which uses numeric columns).

The best is to use numeric keys for your dimension attributes whenever is possible.

For one, it speeds up processing operations , especially having large dimensions. It helps Analysis Server to build optimized dimension stuctures that should lead to better query performance.

It is good practice in general to use numeric ( integer ) keys for the attributes.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you, Edward.

Is there any recommended member count threshold, above which integer keys should be considered? Obviously, having a separate integer key column for each attribute hierarchy could be counterproductive.

|||

Based on my observations. And I could be wrong :)

I wouldnt bother with numeric keys if you talking about few tens thousands members. With hundreds of thousands this could be a problem.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

sql