Prevent local cube from being Refreshed

Question

I have a local cube file that I generated directly from its relational source data, using the technique described by various other parties, such as: http://cwebbbi.wordpress.com/2006/09/19/creating-as2005-local-cubes-with-xmla/

http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/96ad96db-cd6e-4b93-9898-126735703308

(This method uses the ADOMD.NET AdomdConnection and AdomdCommand objects, along with an XMLA "create cube" script, to generate the local cube file directly from the relational source data, without SSAS in play at all.)

Generating a cube this way works flawlessly for me. I can browse the resulting local cube file just fine from a MS Excel Pivot Table (Excel 2007 as well as 2010).

Interestingly, when I use Excel's "Refresh" option (Data tab, "Refresh" button), it even appears to refresh the cube from the relational source.

Is there a way to PREVENT the cube from being Refreshed from its relational source? (The MS SQL Server that holds the source data uses SQL Server Authentication, so the local cube file must have a copy of the original ConnectionString including the authentication credentials for the source database - perhaps there is an alternate way of specifying the ConnectionString [or at least the password] so that it is not included in the resulting local cube file?)

I know a similar question has been asked on this forum several times before - how to prevent Excel's "Refresh" option from refreshing a local cube from a SSAS source. But mine is about how to prevent the "Refresh" option from refreshing this kind of local cube (generated via XMLA and ADOMD.NET directly from a relational source to a local cube file) from its relational source...

Thank you.

Answer 1

I found my own solution using out-of-line binding. (I think that's the term.)

I could not find any thorough examples of out-of-line Data Source substitution on the web, so here's my attempt to explain it in case it is useful to anyone else. (This is not to be confused with Data Source View substitution, of which there are a few examples on the web.)

PROBLEM

The //ObjectDefinition/Database/DataSources/DataSource/ConnectionString node of your ASSL script specifies the connection string to the source database from which your cube is generated. (In other words, if you are creating a cube from a relational database, that source's connection string is stored at this node.)

Here are the problems with that:

1. That un/pw in the connection string is now embedded in your .CUB file. (I think it's encrypted, but it's decryptable, because Excel will use it to connect to the relational data source if you use the Data -> Refresh option.)
2. Even if I specified domain security in my connection string to get around that security risk, I don't want to - because for my scenario I don't want them to be able to do .CUB refreshes from the source at all. I just want the connection string to be modified to use bogus credentials (and server name).

SOLUTION

When you are using ASSL, you can specify an alternate DataSource definition that will be used for .CUB generation, but which will not be embedded in the .CUB file. (I'm unclear if this is in fact out-of-line binding by Microsoft's definition, or if it just a similar concept.) You do this by providing an alternate DataSource (with the same ID as you have at //ObjectDefinition/Database/DataSources/DataSource in your ASSL script). The XML schema for that node is the same - and you place it either as a child of //Batch or //Batch/Process.

So in my case:

1. I cloned the //ObjectDefinition/Database/DataSources/DataSource node
2. In the clone, I substituted a new /ConnectionString node with the string I wanted to use for my real connection string
3. I inserted the cloned node as the last child of //Batch.
4. (Since one of my personal requirements was to prevent Refreshes via Excel, I also substituted a bogus connection string in the original //ObjectDefinition/Database/DataSources/DataSource node for good measure so that if a user tries to Refresh, it directs them to a non-existent server.)

By way of example, my //ObjectDefinition/Database/DataSources/DataSource node looked something like this to start with:

<DataSource>
  <ID>myDS</ID><Name>myDSnm</Name>
  <ConnectionString>Provider=SQLNCLI10.1;Data Source=myserver.mycompany.com;User Id=;Password=;Initial Catalog=myCat</ConnectionString>
  ...
</DataSource>

I cloned that whole DataSource node, injected the proper User Id and Password into the ConnectionString, inserted the whole containing cloned DataSource node as the last child of //Batch:

<Batch>
  ...
  <DataSource>
    <ID>myDS</ID><Name>myDSnm</Name>
    <ConnectionString>Provider=SQLNCLI10.1;Data Source=myserver.mycompany.com;User Id=myCUBgenerationuser;Password=myPassword;Initial
 Catalog=myCat</ConnectionString>
    ...
  </DataSource>

</Batch>

(And as I mentioned, I also went back and changed the myserver.mycompany.com in the original node's ConnectionString to a bogus server name for good measure. No example of that here.)

OTHER USES OF THIS TECHNIQUE

If you need to provide a wholly new connection string, you can. You don't have to just substitute userid/password values. So this is also good if you need to generate the same .CUB file from different databases (say, a dev-test-prod scenario).

Answer 2

Hi Gregg,

Thank you for sharing your solutions and experience here. It will be very beneficial for other community members who have similar questions.

Thanks,
Eileen

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

Answer 3

Thank you, Eileen.

If you have an clarifications on the terminology I used (particularly with regard to out-of-line binding), please let me know and I can update my response to use the proper terminology.