Access SSAS cube from across domains without direct database connection

Question

Hello,

I'm working with SQL Server Analysis Services for the first time and have the dilemma of working on a project in which users must be able to access SSAS Cubes (via a custom web dashboard) that live across different servers and domains, but without having access to the other server's SSAS database connection strings. So Organization A and Organization B will have their own cubes on their own servers, but Organization A users must be able to view Organization B's cubes, and Organization B users must be able to view Organization A's cubes, but neither organization should have access to the connection string.

I've read about allowing HTTP access to the SSAS server and cube from the link below, but that requires setting up users for authentication or allowing anonymous access to one organization's server for users of another organization, and I'm not sure this would be acceptable for this situation, or if this is the preferred way to do this. Is performance acceptable here?

http://technet.microsoft.com/en-us/library/cc917711.aspx

I also wonder if perhaps it makes sense to run a nightly/weekly process that accesses the other organization's SSAS database via a web service or something, and pull that data into a database on the organization's server, and then rebuild the cube. Then that cube would be queried without having to go and connect to the other organization server when viewing the cube.

Has anyone else attempted to accomplish something similar? Is HTTP access the standard way to go for this? Or any other possible options? Thanks, and please let me know if you need more info, still unclear on how some of this works.

Answer 1

Hi

I am trying to do the same thing.

Using ssis to get the total figure from the cube then rename the file to the total figure. Then eexporting the file to the other domain for them to pick and verify with thier siss total figure from thiee cube.

Exports are done through sftp servers

What you reckon ?

Answer 2

HTTP is probably the best option for what you sound like you are trying to do. if they are two machines on same network but not same domain, using ipaddress\username on each (same user/pass) will work, like old school windows networking in workgroups.

You could also just backup , ftp and download/restore the cube on the other machine, might work for what you are doing.