Generally you will want to select all dimension and fact tables and deal with the partitioning later in the cube, so you can go ahead and do this.
Multidimensional cubes and transactional databases are two very different things.
From experience I have seen even veteran DBAs avoid the subject of cubes completely because it is too much of an unknown area for them.
Once you have a data source view set up you can create a cube. In this wizard you should choose to “Use existing tables”. The wizard will then automatically detect fields that can be used as measures. Assuming your foreign keys were correct in the DW or you did the explicit relations correctly in the data source view, the wizard will now automatically select the necessary dimensions. In order to create it on your Analysis Services server you first need to tell Data Tools where your server is located.
You will then be prompted to select your “Measure Group Tables”. In order to explicitly declare the relations between tables the easiest way is to drag and drop the joins between the table key / foreign key in the interface / data source view model. This is done by clicking on Project [Your Project Name] Properties and then going to the deployment tab.
The problem: My SSRS report based on my Cube is not getting the latest data from the table associated with the Cube.
How do I either a) tell the report to use the latest table data, or b) delete any report cache file that might be preventing a table data refresh? I've updated a table structure and its data using queries inside the sql management studio.
This is done by right clicking on the Data Source Views folder and adding a new one.
The wizard will ask you which connection you would like to use and which fact / dimension tables you want to base your cube on.
Some examples of advanced features are calculated measures (to create year to date sums or current month sums that are pre-calculated), hierarchical dimensions which can be used to order dimensions and allow for a more meaningful drill down to the data a user may need.