thanks for the feedback.
Are you saying that I need to have a database on my database engine? As you may know, I already have 2 databases on the analysis server. For example, Adventure Works DW 2008R2.abf was downloaded and restored to AdventureWorksDW2008R2 on my analysis server(first database), as per the previous attachment. Then, a project was created to import the database by using the 'import an existing SQL Server Analysis Services database' (namely AdventureWorksDW2008R2). This created a database called 'Analysis Services Project 2' on the analysis server (also visible in the attachment). So that is a total of 2 identical databases on the analysis server. I think you are suggesting that I only need one database on the analysis server, which you refer to as the 'analysis services database'.
I think you are suggesting that I create a 2nd database, that resides on the database engine and that is what you refer to as the 'Adventure Works DW SQL database'.
Q1: Where do I find/download the 'Adventure Works DW SQL database'. Does it need to be 'paired' with a particular Adventure Works DW 2008R2.abf. By 'paired', I mean, for the Adventure Works DW 2008R2.abf I have restored to the analysis server, is there a specific 'Adventure Works DW SQL database'? I found http://msftdbprodsamples.codeplex.com/releases/view/59211
which lists lots of different download links but I think I need the link :-AdventureWorksDW2008R2 Data File
source code, 74944K, uploaded Apr 3, 2012
-which downloads Adventure Works DW 2008R2.mdf
Q2: I am not clear on what you mean by 'When you import the Analysis Services database in BIDS, make sure you set the deployment properties of the project and point it to the server you want to deploy this too. Also make sure you verify the Data Source connection is valid and the impersonation is set to a login that has access to the SQL database.
'. I think you are saying, once the 'Adventure Works DW SQL database' is installed on my database engine, the data source connection should be pointing to this database and NOT pointing to the Adventure Works DW 2008R2.abf that I have restored to the analysis server. Please confirm?
Q3: Currently, I have 2 identical databases on the analysis server. Do I need both the identical databases on the analysis server? I am struggling to understand why the project 'Analysis Services Project 2' needed to duplicate the original database - I suspect I didnt need to create a project in this manner but my thinking was that this was how you gained access to the cube definitions that come out of the box with the .abf file i.e by creating a project. I suspect that I should just rename 'Analysis Services Project 2', something like 'out of the box AW DW', so that I will always have a project that works, (assuming that I can get it to deploy without errors).
It should be noted that all the databases and servers (engine and analysis) reside on a single pc, to which I have administrator rights and I intend to impersonate as myself in all data source connections. This is a development environment and so I have scope to configure all aspects of the environment, if required.
I am new to SSAS but my understanding NOW is that the way it works is - that if you were starting from scratch trying to create a cube for say a regular production database in the engine(DB1), you then use BIDS to create an analysis server project that uses the DB1 as a data source. You then create cubes, dimensions etc within the project whereby the data source, cubes, dimensions etc get stored in a database on the analysis server (DB2). When data changes occur in the DB1 database and an individual subsequently runs a cube report, it will use the properties of the cube in the DB2 database to drive the functionality of the report and connect to DB1 (using an impersonating account) to retrieve the raw data from DB1.
Any insights greatly appreciated!