Sql-server – Regenerating a cube relational schema in Visual Studio

sql serverssasvisual studio

I have an Analysis Services cube project that my company received from an outside contractor, and I'm trying to get it so that developers can work on it on their local machines.

I'm pretty sure that the original developers generated the schema for the backend database from the cube project and then worked against that. Therefore, I'd like to generate the schema from Visual Studio, rather than just exporting the database in SQL Server Management Studio. Currently, I'm receiving an error saying that the dimensions are bound to user tables when I try to generate the relational schema.

Is my approach reasonable, and if so, how would I go about doing this? If my approach isn't reasonable, why not?

(NOTE: I'd asked this question a few days ago on StackOverflow. When I came back to it, it occurred to me that this would be a better place for it, so I moved it here.)

Best Answer

That's not how it works. A .dsv (data source view) is generated by defining which tables/queries you want to use in your cube.

The flow is:

  1. create one or more .ds data sources by defining how to connect to the source databases
  2. create a dsv (data source view) by adding tables and named queries defining how to get data from your .ds
  3. create dimensions and cubes by defining how the data from your .dsv needs to look in the multidimensional model.
  4. process your dimensions and cubes to load them with data from the source system.

Technically I suppose you could recreate the source systems schema by analyzing the xml in the .dsv file as it contains the data types and table names/queries, and you could theoretically map those back and recreate the source database(s), but that would require a lot of manual work, or you writing something to parse that out of the xml.
If your DSV contains named queries you would still need to analyze those sql queries and reconstruct the underlying table.
Depending on how complex your named queries are this could become near impossible.

After all that work you would end up with a schema and no data, so your developers would maybe be able to modify the visual studio solution, but have no way to load it with data to test their modifications.

Related Question