Mark was kind enough to let me borrow his corner of the blogoshpere to pass along some fun information about Analysis Cubes.
Yesterday, I was working at a client (a public, very well known company running Dynamics GP as a financial package) that has a very complex internal environment on which they wished to run Analysis Cubes for Excel (ACE). We had:
1. A GP transactional Server running in a remote data center
2. A SQL Cluster with two physical machines and two virtual nodes setup in overlapping active / passive cluster (A is active, B is passive but B is also active with A as passive). The SQL Cluster would serve as the second tier in the ACE installation running both Analysis Services and storing the Data Warehouse. This means SSIS would also run on this machine.
3. Both were 64 bit OS
4. Several TSE machines, all 32 bit.
5. No domain priviledges, no sa user access, but my doman user id was granted access as sysadmin to the GP transactional server and to the SQL Cluster.
6. And, lastly, the SQL Cluster Virtual Machince (node) which would run the data warehouse, SSAS and SSIS was a named instance rather than a default instance.
One thing: I am not a MCSE, nor do I have tremendous amounts of hardware/infrastructure/high performance clustering background, so forgive me if some details around the clustering don't make sense to a more educated reader.
As you know, you cannot install ACE on a 64 bit machine. To compound matters, ACE, by default, won't install into a cluster because SSIS is not cluster aware. This causes a problem with the ACE install when it tries to create the SSIS packages - you get a "login…