August 21, 2015 at 9:36 am
I am new to the SSAS scene. We are running SQL Server 2014 EE SP1.
I would like to know more about actual duties and the separation of duties between DBAs and Developers when working with SSAS Multi-Dimensional (MD) Models and SSAS Tabular Models. I am playing around with this and have set up a default instance of Analysis Services (MD Model) and a named instance of Analysis Services (Tabular Model). My limited research/knowledge on SSAS suggests that in Visual Studio, the developers would create their MD Cubes/Tabular Models and only need permissions to the data source (Data Warehouse) to pull the data into their MD Cubes/Tabular Models. The developers would not need access to the SSAS Instances. Then after the developers have developed their MD Cubes/Tabular Models, the DBA would deploy them to the appropriate SSAS Instance (MD Model or Tabular Model) using the SSAS Deployment Wizard. In this deployment, the MD Cubes/Tabular Models are converted to databases on the appropriate SSAS Instance.
Please let me know if the above assessment is accurate and/or provide corrections.
Other questions:
Is the SSAS Deployment Wizard the best tool or only tool to used to deploy both MD Cubes and Tabular Models or may other tools like Visual Studio and Excel be used?
I heard that in Visual Studio, the Tabular Model creates a workspace (which is on the SSAS Instance). Is this correct? Does this means that if the developer is working with the Tabular Model within VS that he/she would need access to the SSAS Instance? If so, what kind of access?
If the developers creates the Tabular Model in Excel, would they require access to the SSAS Tabular instance?
I am still researching and playing around with SSAS. Thanks in advance for all advice/suggestions.
September 3, 2015 at 5:32 pm
The developers would not need access to the SSAS Instances.
The one exception to this is with a tabular instance. When developing tabular models, you need access to a "workspace", i.e. a machine/server with SSAS in tabular mode. If the developers will have this installed on their own machines, it would be ok...if not, they may need access to a test server with SSAS tabular installed.
Is the SSAS Deployment Wizard the best tool or only tool to used to deploy both MD Cubes and Tabular Models or may other tools like Visual Studio and Excel be used?
As with everything else in SQL Server, you can also generate scripts and deploy those.
I heard that in Visual Studio, the Tabular Model creates a workspace (which is on the SSAS Instance). Is this correct? Does this means that if the developer is working with the Tabular Model within VS that he/she would need access to the SSAS Instance? If so, what kind of access?
See my first comment. Developers would need access to a tabular mode instance for their workspaces.
If the developers creates the Tabular Model in Excel, would they require access to the SSAS Tabular instance?
If you create a Power Pivot model in Excel, you do not need a Tabular instance because your "workspace" is the Excel workbook and your own machine's memory. If you want to convert that Power Pivot workbook to a Tabular model in Visual Studio, you would need to define a Tabular instance as workspace.
Hope this helps.
September 3, 2015 at 10:03 pm
Great question! I've also been interested in this divide especially as BI becomes more commonplace.
As operational DBAs (looking after a few hundred instances) we consider our role to be the installation and ongoing maintenance of SQL Server; but post-installation once things drop into the add-on level we only get involved where we feel we're adding substantial operational value.
The team knows MDS because it's in use and we manage back-end infrastructure like the password changes for IIS because developers rarely know how. But we don't manage the deployment of the packages. And we know SSRS because it's in use and we backup the encryption keys because it's something nobody ever thinks of. But we don't manage the deployment of reports.
When it comes to SSAS we feel the same way. While we could learn to deploy cubes we'd only be doing it because we'd feel we have to and not because we're adding value. Not to mention... it would upset the developers and there's enough for them to be upset about!
I did recently ask http://www.sqlservercentral.com/Forums/Topic1714523-17-1.aspx about taking ownership of the operational parts where we feel we can add value (backups, also backups of encryption keys). IMHO that's where we're planning to draw the line.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply