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.
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 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.
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.
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.
No comments:
Post a Comment