November 2007 - Posts
Today, I was trying to create a simple dimension, but kept getting the error "Attribute key cannot be found". Since I was processing a dimension without any relationship to another table, I was very surprised to see this error. It appeared that there was one record that had a column containing the character 'ë'. When I changed this to the character 'e', everything worked. So, after checking the collation I saw that the relational database was Latin1_General_CP1_CI_AS and the cube was Latin1_General_CP1_CI_AI.
So, in order to fix this:
- Open the management studio
- Connect to the Database Engine
- Right click the Instance and select properties to see which collation is used
- Connect to the Analysis Services
- Right-click the Instance and select properties
- Click on "Language/Collation"
- In my case: check the box "Accent-Sensitive"
- Click Ok
- Restart the SQL Server service
- Restart the Analysis Services service
- Re-deploy the Analysis Services database
After that it worked. I guess the person who installed the software didn't select the default settings (?)
I'm writing an sql script that automatically inserts "Unknown" records in dimension tables. Since there are foreign key constraints in the datawarehouse, I needed to have a list of tables in the right order, so I wouldn't get an integrity violation. The following scripts seems to be the answer:
WITH AllTables (TableId, TableName, ParentTableId) AS
(
SELECT
so.id,
so.name,
sr.rkeyid
FROM sysobjects so
LEFT OUTER JOIN sysreferences sr ON sr.fkeyid = so.id
WHERE so.xtype = 'U' AND so.name LIKE 'Dim%'
),
TableList (TableId, TableName, ParentTableId) AS
(
SELECT
at.TableId,
at.TableName,
at.ParentTableId
FROM AllTables at
WHERE ParentTableId IS NULL
UNION ALL
SELECT
at.TableId,
at.TableName,
at.ParentTableId
FROM AllTables at
WHERE at.ParentTableId = at.TableId
UNION ALL
SELECT
at.TableId,
at.TableName,
at.ParentTableId
FROM AllTables at
INNER JOIN TableList tl ON tl.TableId = at.ParentTableId
WHERE at.TableId <> at.ParentTableId
)
SELECT *
FROM TableList
NOTE: I used a WHERE-clause on the table names, since I use a naming convention where dimension tables always start with 'Dim'
I needed to have a column that has a fixed length of 5 characters. The rest had to be filled up with zeros. Example:
SELECT
m.MemberId,
RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, m.MemberId), 5) AS MemberCode
FROM
Member m
Output:
MemberId MemberCode
1 00001
2 00002
3 00003
9 00009
10 00010
11 00011
Just installed the SQL Server 2008 July CTP and started to play... Thought it'd be nice to throw it at you here... the first thing I did:
- I created a simple table Member(MemberId, MemberName, MemberType) in my new Test database and put some test data in it.
- Fired up the BIDS and created a new Analysis Services project
- Created a new dimension and saw some new things:
- I just wanted to create a dimension on the Member table, so I clicked Next a couple of times to finish the dimension
The first thing I saw - besides the new tab page for Attribute Relationships - was the blue line under Member. This was caused by the following message: "Create hierarchies in non-parent child dimension". So... I dragged the MemberType and MemberName property of my dimension to a hierarchy and gave it the name "MemberType - MemberName".
The blue line was still there, but now saying: "Avoid visible attribute hierarchies for attributes used as levels in user defined hierarchies". In order to make sure this warning goes away, you need to change the AttributeHierarchyVisible property of the attributes to False.
Since I created a hierarchy, I now need to define attribute relationships. This is easily done on the Attribute Relationships tabpage (I really like this new feature):
Be sure to set the appropriate relationships to Rigid. Not sure for which ones this has to be done... I now changed the bottom one and the warning about this (another blue line on the Dimension Structure tab) disappeared.
Also, I changed the key column of the MemberName attribute by adding MemberType to it, to make MemberName and MemberType a unique combination. Although I'm not sure if this is still needed in Sql2008 because I didn't see any warning for this...
After processing the dimension and browsing the data everything looks fine. I must say that I like the new features so far and I will look into all new stuff in the near future to see if I can find out how it works.
A quick reminder for myself, because otherwise I forget where to do this:
On the calculation tab, click Calculation Properties
Enter a name for the DisplayFolder of the calculations:
The result:
When you're using calculations in your cube, don't forget to set the "Non-empty behaviour" property of the calculation to give a little boost to the performance...
