4

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.)

Improve this question
  • 2
    You changed the question significantly and then added a bounty. This might have been fine, if there wasn't already an answer. I think it would have been much better, if you had only added a bounty or post a new question (and add the bounty there), leaving this question as it was. I'm flagging it for mod intervention. – ypercubeᵀᴹ Apr 29 '18 at 18:30
  • 1
    @julian On balance, I agree you should ask the refined question as a new question (it is up to you if you re-accept Tom's answer here). I have rolled back your question to the originally-answered version and refunded the bounty so you can apply it to your new question. – Paul White♦ Apr 29 '18 at 21:46
  • 1
    Excellent, thank you. I was expecting it to be deleted if I asked it as a new question. I've posted the new question here: dba.stackexchange.com/q/205362/128086 . Please let me know if there's any way I can improve it. – Julian Goldsmith Apr 30 '18 at 14:19
8

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.

Improve this answer
4

The only time you need to use Schema Generation is if you did top down development and have never generated the schema before. Now that you have previously generated the SQL schema, you can just script out all the objects in SSMS and provide that script along with the SSAS project to other developers.

Then the new developer would deploy the SQL scripts and edit the data source in the SSAS project to point to their database.

This is the most straightforward approach I believe.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.