Hi,
Okay, I am stuck here with this error and other related errors. before anything I will describe the scenario. Please do tell me What is wrong, How it happened and how to correct it.
Initially I had 3 relational tables in SQL server.
1. Dim_A (col1a, col2a, col3a)
2. Dim_B (col1b, col2b)
3. Fact_data (col1a,Col1b)
There are no Primary keys, foreign keys and relationships defined in the SQL relational database between these tables.
I created a OLAP database by:
1. creating the datasource
2. creating the datasource view and setting the logical primary keys and relationships as follows:
a. Fact_data.col1a references -> Dim_A.col1a
b. Fact_data.col1b references -> Dim_B.col1b
3. Next, I created a Cube, without automatically creating attributes or hierarchies (no auto build)
4. A Measure Group was automatically created, with a measure called Fact_data count (which i think is the count of the number of rows and was equal to 145). I checked the relational table "Fact_data" to find that the fact table had indeed 145 rows.
5. Next I built the project with not errors, then deployed and processed it.
6. I had no errors and I was able to browse the cube in the SQL BI studio.
After all this, I had created a asp.net page to issue the "Process" command to the complete OLAP database. (I had used the Process Script XMLA from the BI studio). This was also running perfectly.
Now the problem, suddeny next day when i ran the Process from my Asp.Net page, it gave me errors saying that there are Errors in the OLAP Storage engine. The attribute Key cannot be found, with the Table name, Column name and the value. It also gave other errors saying: The record was skipped because the attribute key was not found. it gave the info of the attribure, Dimension, database, cube, measure group and Partition.
I checked the SQL relational tables to find a change in the "Fact_data" table structure. Here is the changed structure of the Fact_data table:
Fact_data (col1a,Col1b,colNewC)
A new column "colNewC" was added to the table. Actually this was a new table with the same name as the old one with a new column. the old table was renamed and probably changed by some other person.
so i thought that the change in structure was the problem.
I created a new Analysis services project in the SQL BI studio, repeated the steps stated above to create a OLAP database. Then I built the project with no erros.
Next while trying to deploy and process the project. It is giving me the same errors. Attribute Key not found. I am neither able to deploy only or deploy and process.
Next I changed the error handling settings and set it to convert the not found keys to unknown. now it gave me esactly 145 errors, with the No attribute keys found. but with deployment and processing complete.
Now trying to browse the cube gives no records at all.
I analyzed the tables and its records, I felt that this was because the records present in the Fact_data table had no matching records in the Dim_A table. or specifically there were no matching "col1a" columns between them. thus all the 145 rows of the Fact_data table were shown as errors.
My question is:
1. So is this only a data in-consistency issue?
2. When somebody changed the Fact_data table structure, they also changed the data which had no matching columns with the dim_A table? is this correct? and hence the errors.
3. The only possible way to correct this is to populate the correct records, is this right?
Sorry for the lengthy post, but i thought it would describe the scenario better. Please do give a detailed answer for this and also how to correct it.
Thanks and regards
Try defining primary key-foreign key relationships in your SQL database.
This should help you to deal with referential integrity errors you are seeing.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
When running the XMLA Script run the default refresh of the dimesions first then process the cube for a full refresh..
The Error means that the Fact tables foriegn key doest match with the Primary key of the Dim Table AKA the lookup table (DIM) doest have a value to match with the DIM Table.Hope this Explains.
If there are lotsa invalidated data in the fact table change the Error procession configuration to notify and continue.
|||I got exactly the same error message as your post here when I tried to process and deploy the cube in BIDS, did the answer posted here help you solve the problem above?
I want to knwo if that helped solve the problem then I may try this solution.
With best regards,
Yours sincerely,
No comments:
Post a Comment