Thanks for explaining it Steve. So when you restore Sales.abf to sales, SSMS said "Unable to read from the transport connection: An existing connection was forcible closed by the remote host." Where as when you restore the abf to Sales2, SSMS said "MemberKeyUniqueElement cannot appear under Load/ObjectDefinition/Dimension/Hierarchies/Hierarchy. An error occurred when instantiating a metadata object from the file, \Sales2.0.db\Dim Date.1.dim.xml'. An error occurred when loading the Sales cube, from the file, \Sales2.0.db\Sales.2.cub.xml'.
At first I was suspecting that perhaps it was because you were restoring SSAS 2008 backup into SSAS 2005 server, but when I checked, the sales.abf file that I just attached was a SSAS 2005 backup. I also tried restoring this sales.abf to both SSAS 2005 and SSAS 2008 server and had no problem restoring them. So it's not because of version. I once restored an SSAS 2008 backup file into SSAS 2005 server and the error message was something like 'compability' problem, rather than transport error or metadata error. What version of SSAS do you use Steve? If it is 2008, which CTP? Is this server local on your PC or remote on another computer? Which edition is the SSAS, i.e. developer, standard, or enterprise? I think if we want to pursue this avenue (the transport and metadata error), we need to understand the situation of your SQL Server/SSAS environment.
But now that you have successfully created the sales DB on SSAS, and have created the SQL Server tables and populated them. You can process the sales DB. Process means populating with data and calculating the aggregates. To do this, you need to repoint the data source on the sales DB to your SQL Server tables. This is done by expand data sources folder, right click on sales data source and choose properties. The connection string would be something like: "Provider=SQLNCLI10.1;Data Source=MySQLServer;Integrated Security=SSPI;Initial Catalog=Sales". Click on the elipsis button (...) on the Connection String. Set the Provider as "SQL Server Native Client", Server name = your SQL Server, authentication: Windows, database name: Sales DB that was created by the DDL.sql. Click on the Test Connection button to verify. Click on the ... button on the Impersonation Info and choose Use the Service Account. Click OK.
Now that the data source is pointing to the right tables. Right click the sales DB (not the sales DS, sales DSV or sales cube) and choose Process. Ensure that the Process Options is "Process Full". Click on OK to begin processing the sales DB. It should say "Process Succeeded". Click on Close.
Expand the cube folder and right click on the sales cube (not the sales DB, sales DS or sales DSV) and choose browse. You should now be able to browse the cube.
Note: please ensure that the tables are fully populated (contains data) before you process the SSAS DB. This can be done by executing the population scripts.