Lets have a small discussion about one of the most important options available in SSAS Data Source View i.e “Refresh”. I have never given or taken an interview with out discussing about this option. This is such an important option available in DSV. When ever there are changes occured in the schema of the underlying data source then yu can use REFRESH option in dsv to incorporate all the changes to the tables in the DSV. Refreshing a DSV updates the whole data source view.
You can find the option available in the RIGHT CLICK on DSV. PFB the screenshot showing the same.
Lets assume we have 10 tables added in DSV to design a cube and if there are any schema changes occurred in database level for these 10 tables. If you select on REFRESH option then it will go to the database and check for the changes occurred in these 10 tables and update all the changes to the tablels in DSV. The type of schema changes include –
- Primary key defined or removed.
- Foreign key relationship defined or removed.
- Column Added or Removed.
- Table Removed.
To demostrate the same I added one column “TEST” to DimCurrency table which is part of the DSV I designed and removed one column “DISCOUNTPCT” from the table DimPromotion and then I select Refresh in DSV and PFB the screenshot showing the list of changes occured in my underlying data source.
Be careful in refreshing the DSV. You can either update all the changes or reject all the changes. We cannot update Few changes ignoring the remaining. If you defined an attribute/measure using a column and that column is removed then it will remove that attribute/measure from the cube. You can you “Save Report” option available in the wizard to save the report about the changes that are updated on Refreshing DSV for your future reference.
Refreshing a data source view is based completely on the names of the underlying objects. Therefore, if an underlying object is renamed in the data source, Data Source View Designer treats the renamed object as two separate operations—a deletion and an addition. In this case, you may have to manually add the renamed object back to the data source view. You may also have to re-create relationships or logical primary keys.
That’s it and Happy coding !
Roopesh Babu V