Auto fill in colums trough foreign key relationship

Hi,
I have a table users where there is a user_id and an department column.
Also i have a table called KRS where there are the same columns, when a userid is given i want to auto fill in the departmentid,
Can someone help me with this?
Cheers WimYou want help creating a denormalized database?

[sigh...]

If you absolutely need to do this, you can accomplish it through an INSERT trigger on your KRS table.

Why are you duplicating data like this?|||You want help creating a denormalized database?

[sigh...]

If you absolutely need to do this, you can accomplish it through an INSERT trigger on your KRS table.

Why are you duplicating data like this?I keep trying to shoot myself it the foot, but the rifle wobbles when I pull the trigger. Can one of you guys hold it for me?

I actually thought about trying to formulate a reply to this question, but nothing I wrote seemed fit to post. I'm thinking that a VIEW would make life a lot easier in the long run, what do you think?

-PatP

Auto Commit off and getGeneratedKeys

I have AutoCommit turned off, as I am managing commit and rollback.

I also need to retrieve an identity key. Using the statement.getGeneratedKeys throws the error "The statement must be run before the generated keys are avalilable".

Ok, I can understand this, as I am trying to get the identity key before I am commiting the transaction. However I am inserting the parent row, then based on the identity key, I am inserting several children, all within the same transaction.

So how do I do this?

I d/l the latest JDBC drivers and using Java 1.5Sooooo, I need to commit the parent, then do the children within a transaction, and if anything blows up I rollback the children, then do an explicit delete on the parent? What if the app blows up, I now have a parent without the requisit children.

Sort of defeats the whole transaction thingy.|||

Hi busybee123,

I am a little confused about what you are trying to accomplish. Some sample/pseudo code would be great for helping us understand your algorithm. Are you trying to perform nested transactions or are you using two separate connection objects?

Here is some pseudo code of what I suspect you what to achieve; correct me if I am mistaken:

Connection conn1 = DriverManager.getConnection(...);

Connection conn2 = DriverManager.getConnection(...);

conn1.setAutoCommit(false);

conn2.setAutoCommit(false);

Statement stmt1 = conn1.createStatement();

Statement stmt2 = conn2.createStatement();

String parentUpdates[] = new String[]{all parent updates};

boolean commitParent;

for(int i = 0; i < parentUpdates.length; i++)

{

commitParent = true;

stmt1.executeUpdate(parentUpdates[ i ], Statement.RETURN_GENERATED_KEYS);

ResultSet keys = stmt1.getGeneratedKeys();

while(keys.next())

{

String aKey = keys.getString(1);

stmt2.addBatch(insert the children here given aKey value);

}

try{

int updateCounts[] = stmt2.executeBatch();

}

catch(BatchUpdateException bue)

{

//process success/failures here and decide if to commit conn1 and/or conn2 or not

if(something went wrong)

{

conn2.rollback();

commitParent = false;

}

else

{

conn2.commit();

}

}

if(commitParent)

{

conn1.commit(); //or delete parent now

}

else

{

//break, continue, or throw here

}

}

Thanks,

Jaaved Mohammed - MSFT

|||A few things contributed to my post.

We were using an older JDBC driver which did not support getGeneratedKeys(), so we were using "insert ...;select SCOPE_IDENTITY() as newkey" with a statement.executeQuery(). However when we started using manual commit/rollback, this does not work as it creates an internal virtual connection which cannot be used with manual commit.

So we upgraded to the latest JDBC driver which does support getGeneratedKeys(). Reading through the new documentation we came across your example code. When we tried it out, we got the above noted error message. Searching the web for the error message produced zero hits.

Eventually we found that the statement.executeUpdate() method can take a second parameter with the value of Statement.RETURN_GENERATED_KEYS. This causes MS SQL to return the generated key which can then be retrieved as per the examples. So our bad :-) In retrospect the error message does explain what happened, but hindsight is always 20/20

A few things:
- the error message returned was not entirely clear as to why it was failing, and the error code number was zero.

- No documentation about the error could be found.

- in the HTML documentation under "Using Auto Generated Keys", in the second paragraph, second to last sentence it states "with the returned column name of GENERATED_KEYS". This is incorrect. Inspecting the metadata of the result set shows that the column does not have any name or label (empty string). The value can only be retrieved by using the index number of 1.

- could the error messages produced by the JDBC library be documented please?

Kudos to the JDBC team for supporting getGeneratedKeys()!|||I'm working on a similar issue and this code seems to help a bit, but i have a question : does getGeneratedKeys not work with a PreparedStatement for which i'm using batch update? I have my code on this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1470935&SiteID=1sql

Auto Commit off and getGeneratedKeys

I have AutoCommit turned off, as I am managing commit and rollback.

I also need to retrieve an identity key. Using the statement.getGeneratedKeys throws the error "The statement must be run before the generated keys are avalilable".

Ok, I can understand this, as I am trying to get the identity key before I am commiting the transaction. However I am inserting the parent row, then based on the identity key, I am inserting several children, all within the same transaction.

So how do I do this?

I d/l the latest JDBC drivers and using Java 1.5
Sooooo, I need to commit the parent, then do the children within a transaction, and if anything blows up I rollback the children, then do an explicit delete on the parent? What if the app blows up, I now have a parent without the requisit children.

Sort of defeats the whole transaction thingy.|||

Hi busybee123,

I am a little confused about what you are trying to accomplish. Some sample/pseudo code would be great for helping us understand your algorithm. Are you trying to perform nested transactions or are you using two separate connection objects?

Here is some pseudo code of what I suspect you what to achieve; correct me if I am mistaken:

Connection conn1 = DriverManager.getConnection(...);

Connection conn2 = DriverManager.getConnection(...);

conn1.setAutoCommit(false);

conn2.setAutoCommit(false);

Statement stmt1 = conn1.createStatement();

Statement stmt2 = conn2.createStatement();

String parentUpdates[] = new String[]{all parent updates};

boolean commitParent;

for(int i = 0; i < parentUpdates.length; i++)

{

commitParent = true;

stmt1.executeUpdate(parentUpdates[ i ], Statement.RETURN_GENERATED_KEYS);

ResultSet keys = stmt1.getGeneratedKeys();

while(keys.next())

{

String aKey = keys.getString(1);

stmt2.addBatch(insert the children here given aKey value);

}

try{

int updateCounts[] = stmt2.executeBatch();

}

catch(BatchUpdateException bue)

{

//process success/failures here and decide if to commit conn1 and/or conn2 or not

if(something went wrong)

{

conn2.rollback();

commitParent = false;

}

else

{

conn2.commit();

}

}

if(commitParent)

{

conn1.commit(); //or delete parent now

}

else

{

//break, continue, or throw here

}

}

Thanks,

Jaaved Mohammed - MSFT

|||A few things contributed to my post.

We were using an older JDBC driver which did not support getGeneratedKeys(), so we were using "insert ...;select SCOPE_IDENTITY() as newkey" with a statement.executeQuery(). However when we started using manual commit/rollback, this does not work as it creates an internal virtual connection which cannot be used with manual commit.

So we upgraded to the latest JDBC driver which does support getGeneratedKeys(). Reading through the new documentation we came across your example code. When we tried it out, we got the above noted error message. Searching the web for the error message produced zero hits.

Eventually we found that the statement.executeUpdate() method can take a second parameter with the value of Statement.RETURN_GENERATED_KEYS. This causes MS SQL to return the generated key which can then be retrieved as per the examples. So our bad :-) In retrospect the error message does explain what happened, but hindsight is always 20/20

A few things:
- the error message returned was not entirely clear as to why it was failing, and the error code number was zero.

- No documentation about the error could be found.

- in the HTML documentation under "Using Auto Generated Keys", in the second paragraph, second to last sentence it states "with the returned column name of GENERATED_KEYS". This is incorrect. Inspecting the metadata of the result set shows that the column does not have any name or label (empty string). The value can only be retrieved by using the index number of 1.

- could the error messages produced by the JDBC library be documented please?

Kudos to the JDBC team for supporting getGeneratedKeys()!
|||I'm working on a similar issue and this code seems to help a bit, but i have a question : does getGeneratedKeys not work with a PreparedStatement for which i'm using batch update? I have my code on this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1470935&SiteID=1

attribute key was not found error

everytime i try to process my cube i get this error:

Errors in the OLAP storage engine: The attribute key cannot be found:

Table: dbo_MCSFinFactData, Column: InvoiceDateDimensionID, Value: 15.

Errors in the OLAP storage engine: The record was skipped because the attribute key was not found.

Attribute: InvoiceDateDimensionID 8 of Dimension: InvoiceDate Fiscal Year 2 from Database:

SRDBAnalysis, Cube: MCSFinancial, Measure Group: MCSFinancial, Partition: MCSFinancial, Record: 10.

I have checked the dimension table and the record with an invoicedatedimensionid value of 15 exists. there are also many records in the fact table that use an invoicedatedimensionid of 15. if the record exists in both tables, why does it say the attribute key was not found?

i have resolved this, only i dont understand why my actions resolved the issue and was hoping someone could explain.

in the error( in the above post) you see the attribute was "invoiceDate fiscal year 2". i just processed the attribute manually, and had to do the same with a handfull of other attributes, and now the cube works. why is this? also , is there a quicker way, as id rather not have to process 20 - 30 attributes manually each time there is a problem!

|||

Here is another thread on the same matter

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1198671&SiteID=1

The ultimate resolution should be fixing referential integrity problems in relational database and not processing dimensions in different order.

BTW, you cannot process a single attribute by itself

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

|||there are no referential integrity issues within my database. i have made 100% sure of this. this database has been in use for 6 years with analysis services 2000, and there were never processing errors, and the processing was set to tolerate no errors. so it cannot be a database issue. im wondering if i just have to recreate the cubes completely in analysis server 2005? this isnt upgrade any more, its just redevelopment of old projects.|||

Analysis Services 2005 is little bit more strict when comapred to AS2000 in the way it is detecting inconsistencies in relational database. If you run SQL Profiler you'd mention that it is also uses different strategy when querying relational database during processing.

Every attribute is processed separately and in case of AS2000 you'd see a one query per dimension, in AS2005 you see multiple SQL queries sent- one per dimension attribute.

See this paper describing new processing architechture: http://msdn2.microsoft.com/en-us/library/ms345142.aspx

As for detecting inconsistensies in relational database, it could be tricky. For instance, the reason Analysis Server wouldnt find a key for attribute member is; you allowed for processing ignore records with repeated key. Set KeyDuplicate to ReportAndStop. In fact set ReportAndStop for every setting in ErrorConfiguration for your processing command.

Here is whitepaper talks about setting error configuration http://msdn2.microsoft.com/en-us/library/ms345138.aspx

And it is also important to review new dimension stucture and see what becomes a source for attribute key column/s and what it is name.

Migration process might have some quirks in some complex cases mapping AS2000 database to AS2005. As for Analysis Server detecting missing keys - every time I run into missing key case it is always comes down to some modeling or data issues.

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

Attribute Key Cannot Be Found: Single Underlying Table!

I've spent quite a bit of time searching this topic and have not come up with an answer that would help my situation.

I'm building a cube from a single table. That table is used to build both the attributes and measures. I sometimes get the "attribute key cannot be found" error even though queries prove that the data indeed there. Often all that needs to happen to fix the problem is to simply reprocess the cube without making any changes.

Any ideas as to how I could fix this problem? It causes a problem as the cube is scheduled and that is failing. I can't be rebuilding the cube manually each day.

Hello! Even if you have fact table dimensions this problem can be related to that you must always process the dimensions first and the rest of the cube later. If you use MOLAP for storage the dimensions will be built as separate objects even if you use a single table as source.

If you process the cube and have activated "process related objects" in the advanced settings dialogue you can process the cube and the dimensions will be processed first.

HTH

Thomas Ivarsson

|||Perfect! That you so much for your help, Thomas. That makes good sense. I've set the Integration Service project to Process Related Objects.

Thanks Again,

Robin Sarac

edit: Spelling mistakes...

Attribute key cannot be found issue after migration

Hi Guys,

I have migrated my cubes from 2000 to 2005. My cubes are getting processed fine (after fixing known issues) but it looks there is problem with table join with date dimension so lots of rows coming as not found.

This is the error/warning I get while processing however cube is processing fine.

Errors in the OLAP storage engine: The attribute key cannot be found: Table: <fact table>, Column: DT_ID, Value: 2005010208. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: DT_ID of Dimension: Date from Database: Risk Reports, Cube: <cube name>, Measure Group: <name>, Partition: <name>, Record: 6.

The error is very simple and I know what is happing. I have date dimensions which is 30 days only and while processing cube is reading 2005 data. Now in AS 2000 when I check SQL while processing cube I see join between fact table and date dimension. but in 2005 I don't see that join. In 2005 it just select from fact table.

The partition is build using fact table. I think I can fix this issue by making partition as query and join with date dimension but I feel it should be done at data source view level not at partition level.

I am not able to find how I can do at data source view level any idea what I am talking about?

Thank you - Ashok

Hey ashok, in your DSV, you can change your fact table to a Named Query, and limit the pull to only have the related data in your date dimension table..

something like:

select field1,field2,field3 from FACT_TABLE where DT_ID in(
select distinct DT_ID from dim_DATE)

I'm not sure if that's what you want to do, or if that's what you meant.. If you want the unrelated data, you could always just ignore the error in your error configuration, too..

Hope that helps?

C.|||

Named Query will work fine. What I was thinking and I am not sure it is possible or not. In Data Source View when we create relation between fact and dimension tables is there any was that we can set that some join will be taken while cube processing.

In other words in my case I want my fact table should always join with date dimension while processing cube even my cube partition is created using fact table only not query or fact table as named query. I like to have some properties setting in data source view so that while processing cube it should join fact and date dimension based on join key set in data source view.

Thank you - Ashok

attribute key cannot be found - regular hierarchy

I am a newbie in Analysis Services. I have just designed a cube, drawing data from a bigger database. In the cube measures are about responses to questionnairies. There are 3 dimensions: question, time of response and user (who responded).

The user hierarchy is a user-enterprise-activity hierarchy.

When the cube is processed I receive the following error when it reaches to process the enterprise part:

Warning 1 Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Activity, Column: Id, Value: {F1B5099A-F1D4-4156-945E-0D733EB71C8C}. 0 0
(then it believes to be unknown, it finds too many errors and stops).

This value is a correct value for an activity Id. But I suppose this means that the link between enterprise and activity doesn't work (it's perfectly ok in the relational database). But I cannot find why.

SELECT
DISTINCT
[dbo_Activity].[Id] AS [dbo_ActivityId0_0],[dbo_Activity].[EnglishDescription] AS [dbo_ActivityEnglishDescription0_1],[dbo_Activity].[FrenchDescription] AS [dbo_ActivityFrenchDescription0_2]
FROM
(

SELECT Activity.Id, LocalizedActivity.Description AS EnglishDescription, LocalizedActivity_1.Description AS FrenchDescription,
LocalizedActivity_2.Description AS GreekDescription, LocalizedActivity_2.NationalCode AS GreekCode, LocalizedActivity.NationalCode AS EnglishCode,
LocalizedActivity_1.NationalCode AS FrenchCode
FROM Activity INNER JOIN
LocalizedActivity ON Activity.Id = LocalizedActivity.ActivityId INNER JOIN
LocalizedActivity AS LocalizedActivity_1 ON Activity.Id = LocalizedActivity_1.ActivityId INNER JOIN
LocalizedActivity AS LocalizedActivity_2 ON Activity.Id = LocalizedActivity_2.ActivityId
WHERE (LocalizedActivity.Language = 'en-GB') AND (LocalizedActivity_1.Language = 'fr-FR') AND (LocalizedActivity_2.Language = 'gr-GR')
)
AS [dbo_Activity]
Processing Dimension Attribute 'Enterprise' failed. 1 rows have been read.
Start time: 24/5/2006 5:19:28 μμ; End time: 24/5/2006 5:19:28 μμ; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Enterprise].[Id] AS [dbo_EnterpriseId0_0],[dbo_Enterprise].[Name] AS [dbo_EnterpriseName0_1],[dbo_Enterprise].[NationalCode] AS [dbo_EnterpriseNationalCode0_2],[dbo_Enterprise].[PrimaryActivity] AS [dbo_EnterprisePrimaryActivity0_3]
FROM
(

SELECT Id, Name, PrimaryActivity, NationalCode
FROM Enterprise
)
AS [dbo_Enterprise]

Can you post some more information about you situation.

Is error you receive is happening during processing of dimension or partition? If you are processing entire cube, please try to process your dimensions firts and then process partition, by partition.

What is the stucture of your user dimension?

I also see some non-english descriptions in the query, where do they come from?

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

|||Thank you very much for the interest in helping.
Sorry for the short delay, my internet connection was just lame this morning.

The problem is happening during cube processing, but also during user dimension processing.

The user dimension comes from this named query (yes it's a bit familiar for those accustomed to asp.net ;)):
SELECT aspnet_Users.UserId, aspnet_Users.UserName, aspnet_Users.Enterprise, aspnet_Membership.Email
FROM aspnet_Users INNER JOIN
aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId

Afterwards in the User dimension hierarcy,
User (...) has Enterprise and User Name as attributes,
Enterprise (..) has Activity, Name_ and National Code as attributes and
Activity (.) has English Description and French Description as attributes

Detailed code in the end of the post

The descriptions, as it is shown in the code in the first post come from LocalizedActivity table. Activity is thus a named query, drawing data from the activity table and the localized activity one (multiple lines for the latter, two for english language, two for french and two for greek - yes I know, it's a strange combination of languages ;)).

[Code for the dimension in case it helps]
<Source xsi:type="DataSourceViewBinding" dwd:design-time-name="fae66f4d-967b-400e-9dd5-fe6ba89a6771">
<DataSourceViewID>Local Sql Server</DataSourceViewID>
</Source>
<UnknownMember>Visible</UnknownMember>
<CurrentStorageMode>Molap</CurrentStorageMode>
<Attributes>
<Attribute dwd:design-time-name="623f18b9-82f5-4641-9b53-e159ef88c0a3">
<ID>Aspnet Users</ID>
<Name>Aspnet Users</Name>
<Usage>Key</Usage>
<EstimatedCount>2</EstimatedCount>
<KeyColumns>
<KeyColumn dwd:design-time-name="7c5aecb6-c79f-489f-b16d-517a00536a99">
<NullProcessing>UnknownMember</NullProcessing>
<DataType>WChar</DataType>
<Source xsi:type="ColumnBinding" dwd:design-time-name="5c6894e9-7c7e-4d9e-a5e0-0db5e73af31a">
<TableID>dbo_aspnet_Users</TableID>
<ColumnID>UserId</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<AttributeRelationships>
<AttributeRelationship dwd:design-time-name="9a225117-5e19-4e93-98ca-1f9c8aecbb34">
<AttributeID>User Name</AttributeID>
<Name>User Name</Name>
</AttributeRelationship>
<AttributeRelationship dwd:design-time-name="4bd453e1-04c5-44d4-925a-07b781710eb9">
<AttributeID>Enterprise</AttributeID>
<Name>Enterprise</Name>
</AttributeRelationship>
</AttributeRelationships>
<OrderBy>Key</OrderBy>
<InstanceSelection>DropDown</InstanceSelection>
</Attribute>
<Attribute dwd:design-time-name="4d504c73-5be5-4643-ad53-5945dbe0093e">
<ID>User Name</ID>
<Name>User Name</Name>
<Type>PersonFullName</Type>
<EstimatedCount>2</EstimatedCount>
<KeyColumns>
<KeyColumn dwd:design-time-name="1e48b643-917b-4cab-ac8e-c2b5b5836ef3">
<DataType>WChar</DataType>
<DataSize>256</DataSize>
<Source xsi:type="ColumnBinding" dwd:design-time-name="6047a2f6-953b-428a-86bf-59a4a716c9bb">
<TableID>dbo_aspnet_Users</TableID>
<ColumnID>UserName</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn dwd:design-time-name="c8a05998-f1cb-4a4b-bfbc-dc6412078020">
<DataType>WChar</DataType>
<DataSize>256</DataSize>
<Source xsi:type="ColumnBinding" dwd:design-time-name="72d2f7aa-9128-4f32-8c99-3393eade848f">
<TableID>dbo_aspnet_Users</TableID>
<ColumnID>UserName</ColumnID>
</Source>
</NameColumn>
<OrderBy>Key</OrderBy>
<InstanceSelection>DropDown</InstanceSelection>
</Attribute>
<Attribute dwd:design-time-name="5eda6b5e-78ef-4c22-8000-3a93dd23d6a4">
<ID>Enterprise</ID>
<Name>Enterprise</Name>
<KeyColumns>
<KeyColumn dwd:design-time-name="9c5e9ce9-d304-4fb3-b1c4-cbffee628798">
<NullProcessing>UnknownMember</NullProcessing>
<DataType>WChar</DataType>
<Source xsi:type="ColumnBinding" dwd:design-time-name="b1fea07e-c3cd-4811-aebc-21d90d834bec">
<TableID>dbo_Enterprise</TableID>
<ColumnID>Id</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn dwd:design-time-name="b50a3458-488b-45a2-8e33-5237b26fe3b6">
<DataType>WChar</DataType>
<DataSize>50</DataSize>
<Source xsi:type="ColumnBinding" dwd:design-time-name="13b0aaa9-c817-46b5-9609-36039dc8e487">
<TableID>dbo_Enterprise</TableID>
<ColumnID>Name</ColumnID>
</Source>
</NameColumn>
<AttributeRelationships>
<AttributeRelationship dwd:design-time-name="4a5648da-1625-4b0d-9911-41c45ab64325">
<AttributeID>National Code</AttributeID>
<Name>National Code</Name>
</AttributeRelationship>
<AttributeRelationship dwd:design-time-name="1f5d1cdb-3872-4d6b-97fc-e81cabd89429">
<AttributeID>Name</AttributeID>
<Name>Name_</Name>
</AttributeRelationship>
<AttributeRelationship dwd:design-time-name="c7be6cbf-f255-420c-8d70-c4b24d80706f">
<AttributeID>Activity</AttributeID>
<Name>Activity</Name>
<Visible>false</Visible>
</AttributeRelationship>
</AttributeRelationships>
<OrderBy>Key</OrderBy>
<InstanceSelection>DropDown</InstanceSelection>
</Attribute>
<Attribute dwd:design-time-name="5fa36ff1-0d08-4336-9bd7-90c15a6baf53">
<ID>National Code</ID>
<Name>National Code</Name>
<KeyColumns>
<KeyColumn dwd:design-time-name="8ec125a2-426e-41d1-bdf2-5c07b540b41b">
<DataType>WChar</DataType>
<DataSize>16</DataSize>
<Source xsi:type="ColumnBinding" dwd:design-time-name="eaeb27bc-d602-4e02-992c-8d619633b365">
<TableID>dbo_Enterprise</TableID>
<ColumnID>NationalCode</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<OrderBy>Key</OrderBy>
</Attribute>
<Attribute dwd:design-time-name="5248c844-b5b4-4946-be3b-cd035c7d36d7">
<ID>Name</ID>
<Name>Name</Name>
<Type>Caption</Type>
<KeyColumns>
<KeyColumn dwd:design-time-name="30bbddd9-ebb1-4570-ac88-1825911cca8d">
<DataType>WChar</DataType>
<DataSize>50</DataSize>
<Source xsi:type="ColumnBinding" dwd:design-time-name="7b76b669-47bf-403e-b5e9-c95480031621">
<TableID>dbo_Enterprise</TableID>
<ColumnID>Name</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<OrderBy>Key</OrderBy>
</Attribute>
<Attribute dwd:design-time-name="94b00c3c-bdb6-434e-93be-59cd8b702c86">
<ID>Activity</ID>
<Name>Activity</Name>
<KeyColumns>
<KeyColumn dwd:design-time-name="7e570409-f592-460b-83bb-7d36bd5c83ef">
<NullProcessing>UnknownMember</NullProcessing>
<DataType>WChar</DataType>
<Source xsi:type="ColumnBinding" dwd:design-time-name="633bf76c-9410-4513-9933-b3439975ce44">
<TableID>dbo_Activity</TableID>
<ColumnID>Id</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<AttributeRelationships>
<AttributeRelationship dwd:design-time-name="376c55ce-bff9-48a1-bc19-0985819f9af8">
<AttributeID>Description</AttributeID>
<Name>EnglishDescription</Name>
</AttributeRelationship>
<AttributeRelationship dwd:design-time-name="612f91ad-e799-4aaa-bf7f-08a13a7a69ec">
<AttributeID>EnglishDescription 1</AttributeID>
<Name>FrenchDescription</Name>
</AttributeRelationship>
</AttributeRelationships>
<OrderBy>Key</OrderBy>
</Attribute>
<Attribute dwd:design-time-name="e6bcad35-b68c-477a-a03a-3f6b5de37d69">
<ID>Description</ID>
<Name>EnglishDescription</Name>
<Type>Caption</Type>
<KeyColumns>
<KeyColumn dwd:design-time-name="6a48f875-38dd-449f-8c4b-a037be5851b4">
<DataType>WChar</DataType>
<DataSize>128</DataSize>
<Source xsi:type="ColumnBinding" dwd:design-time-name="aa6ce269-8d62-408c-b7a9-538eee58708c">
<TableID>dbo_Activity</TableID>
<ColumnID>EnglishDescription</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<OrderBy>Key</OrderBy>
</Attribute>
<Attribute dwd:design-time-name="51245cda-daf1-43d5-9c16-83f03c58c84d">
<ID>EnglishDescription 1</ID>
<Name>FrenchDescription</Name>
<Type>Caption</Type>
<KeyColumns>
<KeyColumn dwd:design-time-name="16b37f49-cbd4-4e2b-ad2f-87053b15083b">
<DataType>WChar</DataType>
<DataSize>128</DataSize>
<Source xsi:type="ColumnBinding" dwd:design-time-name="4fcc5408-b96d-4e24-b3d6-43bb33ca345b">
<TableID>dbo_Activity</TableID>
<ColumnID>FrenchDescription</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<OrderBy>Key</OrderBy>
</Attribute>
</Attributes>
<Hierarchies>
<Hierarchy dwd:design-time-name="80e30aa7-1d98-49a3-ae89-14022ba5ff24">
<ID>Activity - Enterprise</ID>
<Name>Activity - Enterprise</Name>
<Levels>
<Level dwd:design-time-name="aaba10df-78c3-4509-b520-52b6e6fe656f">
<ID>Activity</ID>
<Name>Activity</Name>
<SourceAttributeID>Activity</SourceAttributeID>
</Level>
<Level dwd:design-time-name="05e46e50-bb6e-4f14-b4c8-2609dc5a9fb6">
<ID>Enterprise</ID>
<Name>Enterprise</Name>
<SourceAttributeID>Enterprise</SourceAttributeID>
</Level>
<Level dwd:design-time-name="348e276e-dc64-4665-b8ac-64f5739b40c4">
<ID>Aspnet Users</ID>
<Name>User</Name>
<SourceAttributeID>Aspnet Users</SourceAttributeID>
</Level>
</Levels>
</Hierarchy>
</Hierarchies>|||

It is bit strange...

Your error indicates:

Table: dbo_Activity, Column: Id, Value: {F1B5099A-F1D4-4156-945E-0D733EB71C8C}

But little below from there I see error message: Processing Dimension Attribute 'Enterprise' failed. 'Enterprise' attribute is based on the dbo_Enterprise table.

Processing of which attribute is failing?

Another observation, are you running Enterprise version of Analysis Services? In this case you should be able to take advantage of Translations feature and get rid of extra description in French and Greek.

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

|||The process of Enteprise attribute is failing. I also don't get whiy I have a warning regarding dbo_Activity but a problem in enterprise.
The full list of errors is the following (I now check it again and I put to bold something I find interesting):
Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Activity, Column: Id, Value: {F1B5099A-F1D4-4156-945E-0D733EB71C8C}. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Enterprise of Dimension: User from Database: QueStorm, Record: 2.
Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Activity, Column: Id, Value: {F1B5099A-F1D4-4156-945E-0D733EB71C8C}. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Enterprise of Dimension: User from Database: QueStorm, Record: 2. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while the 'Enterprise' attribute of the 'User' dimension from the 'QueStorm' database was being processed.
Errors and Warnings from Response
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
Errors in the OLAP storage engine: An error occurred while the 'Enterprise' attribute of the 'User' dimension from the 'QueStorm' database was being processed.
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Activity, Column: Id, Value: {F1B5099A-F1D4-4156-945E-0D733EB71C8C}.
Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Enterprise of Dimension: User from Database: QueStorm, Record: 2.

I run Standard version. In any case, I need the French descriptions for the relational part of the application (so the table is already there, filled).|||

The problem I beleive is following:

When processing your Activity attribute, Analysis Server will send a SQL query to read all ActivityId's and will save these ActivityId's as keys for Activity attribute

While processing Enterprise attribute, it will send SQL query for EnterpriseId and also for matching ActvityId to know what is going to be the parent Activity for specific Enterprise.

Try copy SQL query for Activity attribute from processing dialog and append "Where" clause to it with ActivityId you getting from the Enterprise attribute error and send it directly. I suspect you will get an empty result.

The problem could be with the way you defined your Descritpion columns in SQL server. You need to make sure you set the collation correctly on Descriotion columns.

But this just a theory. Try doing the experiment above and see if get any results from the SQL query.

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

|||Try copy SQL query for Activity attribute from processing dialog and append "Where" clause to it with ActivityId you getting from the Enterprise attribute error and send it directly. I suspect you will get an empty result.

Send it directly to where? Try it in Server Management Studio, that is? Or modify the named query for the Enterprise?

Thank you very much, I suspect some recent changes I made to the enterprise description (I tried to use the enterprise name as the description field of the enterprise instead of the key and I suspect it didn't work)
|||When running the query with the above id in the data view query editor, the results are ok (not empty).

Changing the name attribute didn't solve the problem

I send here an image in case it helps:
http://recursive-cacophony.net/tec-goblin/Irrelevant/UserDim.PNG
BTW, in ff this control works weirdly (I cannot use html code)|||

May be the last thing before I give up:)

Try creating new tables for your French and Greek descriptions: LocalizedActivity_1_F and LocalizedActivity_2_G

When creating these tables set collation for Description column to Latin1_General_BIN (I assume you are using SQL Server as your relational database ).

Populate these tables with data from LocalizedActivity_1 and LocalizedActivity_2, change your named query to point to the new tables and try processing dimension again.

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

|||Yeich, this would ruin my architecture - you mean I have to create tables in the data source or in the relational database? I am not considering changing the relational database at this moment. (Btw, greek have their special collation rules - the alphabet is different and they are accent sensitive - and I use this as default collation in the database, without having seen any problem 'till now).
Finally, I am not even using the greek description in the cube.

In SQL Server Management Studio I can see data in all languages perfectly, and all fields are nvarchar, in the Cube they are wchar, I don't see many ways this could be a problem... :S|||Well, I finally found what was the problem. It had not to do with the cube, nor with the collation. It is actually in the select statement I have in the first post:

Using INNER JOIN (as the query designer always does) is a bad idea when sometimes the entries do not exist : for example there are no greek nor english descriptions for the activities right now in the database (it is for future use). So, if you use inner joins instead of nested select statements, you lose all the row, so instead of something like:

[anactivityId] [anactivityFrenchDescription] null null

you have no row at all.

Attribut key not found vs Characters

Hello,

When I process one of my dimension, I have an error message "attribut key not found".

It's not link with records present in fact table and not in dimension table, but with the value of the attributs which contain symbols.

To be accurate I integrate first data from a database DB2 AS400 to sql server 2005 (with provider ADO.NET ODBC).

In the database DB2 AS400 the data could have different languages (chinese, japanese,...) but so far it is not necessary to see data with the correct language (moreover the database is not setup in Unicode and 1 specific language = 1 CCSID value...).

So I could have a client name with symbols in the name like :"€"

I think this is what SSAS doesn't like when I process the dimension...

When I set the KeyNotFound property of the dimension to "Ignore Error", I can after process my dimension, but when I browse it, I have an error ("the server sent an unrecognizable response").

Do you have any idea to how to deal with this error?

I think about eliminating the symbols like "€" to begin, but I don't know if there are better solutions (and less dirty...).

Thanks,

Guillaume

There are two properties on the attribute's key columns you should check: Collation and InvalidXmlCharacters. The first will set the character set and case sensitivity for the column in question. When not set at the column level it inherits from the parent object, usually all the way up to the default settings specified when the OLAP server is installed. This should allow the server to understand when two different character codes represent equivalent or different characters. The second property, InvalidXmlCharacters, determines the behavior of the server when it encounters characters that are not valid in XML without some encoding. For performance reasons, the server will not check for the presence of such characters unless you set the InvalidXmlCharacters property. As a result if such invalid characters exist, the response the server sends to the client will not be valid XML.|||

Thanks for you reply, it works now!

I need to ignore the "attribut key not found" error to be able to process the dimension, but thanks to the option InvalidXmlCharacters set to "remove" I can browse my attributs.

Guillaume