We just upgraded to SQL Server 2005 and would like to allow users to query
against our datawarehouse using pivot tables that connect via external
datasource to the SQL server.
Is this a realistic configuration, given that the users are scattered all
around the USA on a WAN (broadband speeds) and the SQL server is centrally
located?
We tried this with an older version of Excel and SQL server years ago, but
it appeared the query sent far more data to excel that was required for the
pivot.
What I was wondering is if any experts out there would know if the latest
versions of SQL Server and Excel are more intelligent now about performing
the query and summarization on the server, and only sending the resultant
back to the client.
Or is there some other technology alternative we should look at?
Unfortunately, olap cubes are a no-go due to the large number of attributes
in our data that cannot be pre-defined into a reasonable number of cubes.