Creating a Data Source View(DSV) in SSAS
The data source view is the visual implementation of the UDM (Unified Dimensional model in SSAS 2008. The Data Source View (DSV) is the place where you add tables, named queries, views, calculated measure/members, and logical keys to create a "view " of your data sources that the cube can use to map processing and relationships for your cube. Suffice to say, you can't add it to the cube if it's not in the DSV so let's get started building one.
Step 1
Right Click on Data Source View in the Solution Explorer and Select New
You will then choose the data source you created previously. (For steps on how to do this, please see my article on "Creating a Data Source in SSAS 2008"). Once you create the correct data source, you will choose next.
Step 2
Choose any schema restrictions you might have through the Advanced button.
This may be helpful if working with SAP, other ERP software, or even your own DW where you have logical data segregated by schema.
Step 3
Choose the Tables and Views that will make up the model for your cube.
These objects will be added to the DSV model and will be the data structures the cube has available to it for building, processing, data mining, etc..
Step 4
Browse your DSV
Check out the interface your presented with and try moving some tables around, getting used to the interface. You will do a lot of tweaking work in here.
Step 5
Create Named Calculations
A Named Calculations allows you to create new columns in your DSV to represent calculated or concatenated values. A good example is a combination of Name Columns to create a FullName Column for the Customer Dimension. (see below)
Step 6
Change the Names of your Dimensions and Fact Tables to have a new Friendly Name. This will provide for better usability for the developers who are working on the project and using this DSV. This is a great way to help the developers work with users to include the right information from each application.
Step 7
Implement any new logical keys that you will need. Many times you will use views or tables from different data sources that have logical but not physical keys. This can be a real challenge to remember and code for, but in the DSV you can implement them either by dragging the columns on top of one another, or by right clicking on the table and selecting "New Relationship". (see below)
Step 8
Examine your data to make sure it looks as you expect it too. If you right click on any table you can choose "Explore Data". This will give you the screen below.
This will allow you to pivot your data, make simple charts, and scroll through to verify all is as you believe it to be.
Once you've done all these steps, your DSV should be ready to use ! Next you are ready to jump into building your cube. More to come on that soon !
As always, please post your questions in the BIDN forums to have access to the top resources on Business Intelligence and best practices!
Thanks - Adam