DSV Refresh

Hi,

I have an odd problem with the creation of objects in the data source view using AMO.

I added some tables and relations with AMO and everything seems to work well.

Then, using Analysis Services I selected the data source view and refreshed it.

The form that notifies the changes in the data source view showed a lot of changes that I didn’t understand, costraints created and removed, fields changed and other operations that I didn’t made...

I try to do the refresh with AMO using all the update and refresh options I found but with no results.

Every insight on the question will be of great help.

Thanks

This is an extract of the data source view refresh report:

Object

Change

_DIM_DimColl1

Changed

Constraint1

Deleted

_DIM_Dimensione

attrfinto

Changed

_DIM_eta2

aaa

Changed

_DIM_Mesi

Changed

_DIM_MesiColl

Changed

Constraint1

Deleted

_FK_eta2_aaa

Changed

Constraint1

Deleted

_FK_MesiColl_Livello03

Changed

Constraint1

I don't have a clue what's causing that, but let me suggest that you keep a copy of the .dsv (XML) file from before you refresh the DSV. Then you refresh it, save it, and do a diff versus the previous file. Then you can see exactly what properties changed. There's a pretty close mapping between AMO properties and the XML elements in the file.|||The refresh in AMO is different from the refresh in DSV diagram. DSV Diagram refresh will connect to the relational database and refresh the metadata. AMO refresh will not connect to relational database and just refresh the objects within AMO.|||

Do you know how can I refresh the DSV through AMO?

DSV Refresh

Hi,

I have an odd problem with the creation of objects in the data source view using AMO.

I added some tables and relations with AMO and everything seems to work well.

Then, using Analysis Services I selected the data source view and refreshed it.

The form that notifies the changes in the data source view showed a lot of changes that I didn’t understand, costraints created and removed, fields changed and other operations that I didn’t made...

I try to do the refresh with AMO using all the update and refresh options I found but with no results.

Every insight on the question will be of great help.

Thanks

This is an extract of the data source view refresh report:

Object

Change

_DIM_DimColl1

Changed

Constraint1

Deleted

_DIM_Dimensione

attrfinto

Changed

_DIM_eta2

aaa

Changed

_DIM_Mesi

Changed

_DIM_MesiColl

Changed

Constraint1

Deleted

_FK_eta2_aaa

Changed

Constraint1

Deleted

_FK_MesiColl_Livello03

Changed

Constraint1

I don't have a clue what's causing that, but let me suggest that you keep a copy of the .dsv (XML) file from before you refresh the DSV. Then you refresh it, save it, and do a diff versus the previous file. Then you can see exactly what properties changed. There's a pretty close mapping between AMO properties and the XML elements in the file.|||The refresh in AMO is different from the refresh in DSV diagram. DSV Diagram refresh will connect to the relational database and refresh the metadata. AMO refresh will not connect to relational database and just refresh the objects within AMO.|||

Do you know how can I refresh the DSV through AMO?

DSV in SSIS

Hi,
How can we make use of DSV in SSIS?

In the connection manager of a package i tried to add a link to it but couldn't.

I believe it has an importance since it is there. Could anyone tell me what is its benefits and how it could be implemented?

Thanks

Christina

A DSV allows you to create a metadata layer over an existing Data Source. Within a DSV, you can select a subset (or all) of the tables and views available from a Data Source, rename any of the tables/views and/or their columns, add new Named Calculations (which act like computed columns within a table/view), and add new Named Queries (which are complete select statements, which basically act like views).

To use a DSV within SSIS, you still create a Connection Manager that points to the Data Source that the DSV is based on. Then, when you select the Connection Manager, you should have the option to select the DSV that is based on it. For example, if you have a Data Source based on an OLE DB provider connecting to SQL Server, you could create a DSV based on the database that the Data Source is connecting to. Then, within a Data Flow task, if you add an OLE DB Source adapter, when you select the Connection Manager based on your OLE DB data source, you should be able to also select the DSV.

HTH,

Dave Fackler

|||

Thanks Dave.

Actually this is what I read about it in the books online. However when I come to add a data source in the connection manager i don't see the DSV among the possible connections.

Have you tried it? I could have missed something.

Thanks again

Christina

DSV and Cube do not match - generates Key Errors

Hi All,

This is strange behaviour, hopefully I can resolve it without rebuilding the cube and all the dimensions from scratch.

I have a cube with a fact table, and a number of dimensions, including an EventType and Event Date. (Event Type is "Sale", "Return", etc.etc, Event Date is the date it occurred)

When I created the DSV for this I accidently joined the fact table EventTypeID field to the EventTimeID on the Event Date dimensions. Not suprisingly this gave me a key error, as my EventTypeID on the fact table has values from 1-12, and the EventTimeID records start at 10000 and go upwards.

Having seen the error I went into the DSV and changed the relationship so that the Event Date dimension table was joined to the fact table on the correct fields. I then checked using SQL that there were no missing keys or other oddities on the base tables. I then manually did FULL process on all the dimensions, then tried to process the cube.

No dice. The error still occurs, it still claims that there is a missing key on the Event Date dimension, and a little further investigation shows that it is still using EventTypeID as the joining key. I have manually re-processed all dimensions etc, but to no avail.

How do I get the cube to pick up the changes in the DSV? What I don't want to have to do is throw it all away, as there are a fair number of hierarchies etc I would have to re-create.

Any help appreciated.

Richard R.

The Error reported back is:

Errors in the OLAP storage engine:
The attribute key cannot be found:
Table: dbo_tbl_Sales_FACT_LOAD, Column: EventTypeID, Value: 1.
Errors in the OLAP storage engine:
The attribute key was converted to an unknown member because the attribute key was not found.
Attribute Tbl Time DIM of Dimension:
Event Date from Database: ProtoType Cubes,
Cube: Sales And Mailings, Measure Group: Tbl Sales And Mailing FACT LOAD,
Partition: Tbl Sales And Mailing FACT LOAD, Record: 1.

Unfortunately, the metadata is pulled from the DSV and embedded in the higher level objects (dimensions, cubes, measure groups, etc.) when those objects are created. You need to recreate the objects for it to pickup the revised metadata in the DSV. Just updating the DSV doesn't do it alone.

Sometimes if it is a simple change, you can script out the objects into an XMLA script and then recreate it from the script rather than taking the time to drag & drop new objects around the system. However, you have to be careful and be knowledgeable. It is straightforward to do -- you are just editing a flat file, but if there are lots of references and changes I wouldn't go down that path.

Sorry to give you the bad news.

_-_-_ Dave

|||

As the late, great, Kenny Everett would have said

"Oh Bum!"

Thanks Dave,

Richard

|||

I hope somebody releases an XML refresh & validation tool for this issue. I have been troubleshooting this one and other datatype & DSV errors for awhile, and it affects multiple dimensions.

I have found that by correcting the relationship, then going into the cube's dimensions and clicking on the ... next to the dimension, you can reselect the key field (the same field, just click it again) and it seems to do the trick.

Otherwise, it's 'view code' and manually changing the DSV & the objects affected.

|||Wow, I just sank a few hours of time because of this issue...has anyone seen an refresh/validation tool like Andrew indicated? I could easily see how it could save tons of time!

DSV and Cube do not match - generates Key Errors

Hi All,

This is strange behaviour, hopefully I can resolve it without rebuilding the cube and all the dimensions from scratch.

I have a cube with a fact table, and a number of dimensions, including an EventType and Event Date. (Event Type is "Sale", "Return", etc.etc, Event Date is the date it occurred)

When I created the DSV for this I accidently joined the fact table EventTypeID field to the EventTimeID on the Event Date dimensions. Not suprisingly this gave me a key error, as my EventTypeID on the fact table has values from 1-12, and the EventTimeID records start at 10000 and go upwards.

Having seen the error I went into the DSV and changed the relationship so that the Event Date dimension table was joined to the fact table on the correct fields. I then checked using SQL that there were no missing keys or other oddities on the base tables. I then manually did FULL process on all the dimensions, then tried to process the cube.

No dice. The error still occurs, it still claims that there is a missing key on the Event Date dimension, and a little further investigation shows that it is still using EventTypeID as the joining key. I have manually re-processed all dimensions etc, but to no avail.

How do I get the cube to pick up the changes in the DSV? What I don't want to have to do is throw it all away, as there are a fair number of hierarchies etc I would have to re-create.

Any help appreciated.

Richard R.

The Error reported back is:

Errors in the OLAP storage engine:
The attribute key cannot be found:
Table: dbo_tbl_Sales_FACT_LOAD, Column: EventTypeID, Value: 1.
Errors in the OLAP storage engine:
The attribute key was converted to an unknown member because the attribute key was not found.
Attribute Tbl Time DIM of Dimension:
Event Date from Database: ProtoType Cubes,
Cube: Sales And Mailings, Measure Group: Tbl Sales And Mailing FACT LOAD,
Partition: Tbl Sales And Mailing FACT LOAD, Record: 1.

Unfortunately, the metadata is pulled from the DSV and embedded in the higher level objects (dimensions, cubes, measure groups, etc.) when those objects are created. You need to recreate the objects for it to pickup the revised metadata in the DSV. Just updating the DSV doesn't do it alone.

Sometimes if it is a simple change, you can script out the objects into an XMLA script and then recreate it from the script rather than taking the time to drag & drop new objects around the system. However, you have to be careful and be knowledgeable. It is straightforward to do -- you are just editing a flat file, but if there are lots of references and changes I wouldn't go down that path.

Sorry to give you the bad news.

_-_-_ Dave

|||

As the late, great, Kenny Everett would have said

"Oh Bum!"

Thanks Dave,

Richard

|||

I hope somebody releases an XML refresh & validation tool for this issue. I have been troubleshooting this one and other datatype & DSV errors for awhile, and it affects multiple dimensions.

I have found that by correcting the relationship, then going into the cube's dimensions and clicking on the ... next to the dimension, you can reselect the key field (the same field, just click it again) and it seems to do the trick.

Otherwise, it's 'view code' and manually changing the DSV & the objects affected.

|||Wow, I just sank a few hours of time because of this issue...has anyone seen an refresh/validation tool like Andrew indicated? I could easily see how it could save tons of time!

DSV and Cube do not match - generates Key Errors

Hi All,

This is strange behaviour, hopefully I can resolve it without rebuilding the cube and all the dimensions from scratch.

I have a cube with a fact table, and a number of dimensions, including an EventType and Event Date. (Event Type is "Sale", "Return", etc.etc, Event Date is the date it occurred)

When I created the DSV for this I accidently joined the fact table EventTypeID field to the EventTimeID on the Event Date dimensions. Not suprisingly this gave me a key error, as my EventTypeID on the fact table has values from 1-12, and the EventTimeID records start at 10000 and go upwards.

Having seen the error I went into the DSV and changed the relationship so that the Event Date dimension table was joined to the fact table on the correct fields. I then checked using SQL that there were no missing keys or other oddities on the base tables. I then manually did FULL process on all the dimensions, then tried to process the cube.

No dice. The error still occurs, it still claims that there is a missing key on the Event Date dimension, and a little further investigation shows that it is still using EventTypeID as the joining key. I have manually re-processed all dimensions etc, but to no avail.

How do I get the cube to pick up the changes in the DSV? What I don't want to have to do is throw it all away, as there are a fair number of hierarchies etc I would have to re-create.

Any help appreciated.

Richard R.

The Error reported back is:

Errors in the OLAP storage engine:
The attribute key cannot be found:
Table: dbo_tbl_Sales_FACT_LOAD, Column: EventTypeID, Value: 1.
Errors in the OLAP storage engine:
The attribute key was converted to an unknown member because the attribute key was not found.
Attribute Tbl Time DIM of Dimension:
Event Date from Database: ProtoType Cubes,
Cube: Sales And Mailings, Measure Group: Tbl Sales And Mailing FACT LOAD,
Partition: Tbl Sales And Mailing FACT LOAD, Record: 1.

Unfortunately, the metadata is pulled from the DSV and embedded in the higher level objects (dimensions, cubes, measure groups, etc.) when those objects are created. You need to recreate the objects for it to pickup the revised metadata in the DSV. Just updating the DSV doesn't do it alone.

Sometimes if it is a simple change, you can script out the objects into an XMLA script and then recreate it from the script rather than taking the time to drag & drop new objects around the system. However, you have to be careful and be knowledgeable. It is straightforward to do -- you are just editing a flat file, but if there are lots of references and changes I wouldn't go down that path.

Sorry to give you the bad news.

_-_-_ Dave

|||

As the late, great, Kenny Everett would have said

"Oh Bum!"

Thanks Dave,

Richard

|||

I hope somebody releases an XML refresh & validation tool for this issue. I have been troubleshooting this one and other datatype & DSV errors for awhile, and it affects multiple dimensions.

I have found that by correcting the relationship, then going into the cube's dimensions and clicking on the ... next to the dimension, you can reselect the key field (the same field, just click it again) and it seems to do the trick.

Otherwise, it's 'view code' and manually changing the DSV & the objects affected.

|||Wow, I just sank a few hours of time because of this issue...has anyone seen an refresh/validation tool like Andrew indicated? I could easily see how it could save tons of time!