Is there a standard via which one should start an analysis services project? Out situation is that we work locally on our machines, and the relational DB is out of our domain - and the server that analysis services DB will eventually sit on is out of our domain. So, I have a bunch of questions:
Where do you set up the Star schema DB (as the source of the OLAP project)? Is it on a separate server somewhere? And then how do you have a dev star schema db, vs your production star schema DB?
When you create a new Anaylsis Project on your visual studio, where should it connect to (dev star schema? prod star schema?)
Analysis services only supports windows authentication, so how do you get around this if your local computer and the dev analysis services is not on the same server?
When doing ETL work (on SSIS) which DB do you connect to? (dev, I assume) - but then how do you deploy to production?
What about down the line if you need to make changes - how does that process work?
I apologise for haphazard questions, but I'm not really sure where to start, so if anyone has a process from start to finish that is a standard, please let me know.. thanks!
For this one SSAS project that I recently set up, here is what I did:
I have a development PC and a server. Both in the same domain. The development PC is used to edit the VS project. The server is used to host the trial staging / star schema DB, real staging DB, SSAS development cube, and SSAS production cubes.
During development, I use the trial staging DB to test ETL, and deploy to the SSAS development cube.
To build the real cube, I switch the data source to point to the real staging DB, and deploy to a new SSAS cube. Old production cubes are left unchanged so users can still access them while I deploy the new cube. After the new cube is deployed, I will announce the availability of the new cube, and then I can delete the old one. BTW I do this monthly.
If your SSAS server is not in the same domain, you can create an SSAS DB backup, and restore it in that remote server.
To enable Visual Studio to access SSAS server, I had to run visual studio as administrator (right click VS shortcut, 'run as...'). Or you can open the SSAS server with SSMS on the server, then put your active directory user as member of administrator group.