January 22, 2013 at 6:17 am
I'm developing a SSAS 2012 Cube that will be using DB2 as a data source. I don't have any trouble connecting to DB2, but I'm not sure how I will be able to eventually promote this development code into a production environment.
Our development databases are on the "server" DBD1. Inside of there we have many different databases (ex, DEVD, DEVL, BETAD, etc). So queries to build the data source view look like "SELECT * FROM DEVD.TABLE"
When we move from development to production, not only does the server name change, but the database or schema also changes. So my "DEVD.TABLE" changes to "PROD.TABLE". I know how to change the DBD1 server to its production equivalent through the connection string, but I haven't figured out how to change that database/schema from DEVD to PROD without doing a find & replace on the XML for every single query before rebuilding the cube. The "Initial Catalog" option is grayed out in the connection manager for the "Native OLE DB\IBM OLE DB Provider for DB2" provider.
Is there a way through some configuration or variable that I can change the database/schema of my source when moving from a development environment to a different environment? I don't think a find & replace would be an approved deployment procedure when our change management processes get audited. But if I could set that through some sort of configuration file, that would be OK.
Thank you very much for any help you may be able to provide!!!
January 22, 2013 at 7:26 am
What you possible could do is create an abstraction layer in SQL Server. You can create a view for every table, which just selects all the data from the DB2 table using a linked server.
Something like:
SELECT myCol1, myCol2, ..., myColN FROM myLinkedServer.DBD1.DEVD.TABLE
This way your SSAS data source view always stays the same, only the data source changes.
You need to deploy the views once to your production server and do a simple search-and-replace.
Not sure how this affects query performance though.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 29, 2013 at 8:43 am
Thanks for the response! I gave this a whirl, and it seems to be working. We ended up doing OPENQUERY queries rather than views to avoid having to hard-code our DEVL or PROD schemas into the views.
Performance of the queries does seem to be slower than hitting DB2 directly. I'll have to work with the DBAs to see if the query performance is acceptable for what we're trying to do.
Thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply