Connecting to data sources is easy right? You simply use data connection manager, or choose a table from an existing connection right. The "wizards" insert the Connection string into the Load Script and you are off to the races for your queries.
LIB CONNECT TO 'Snowflake_qlik.us-east-1.snowflakecomputing.com (qlikpe_qlikservice)';
Then some yahoo says "Hey Qlik Dork the customer actually has 2 server instances and they needed the data loaded from both." Now you could simply duplicate the section of code and do the same thing for the second instance. It's not that hard after all. A cut here, a paste there, a CONCATENATE before a LOAD there and bingo ... 2 different server instances.
But your Spidey senses start tingling because you know what it really means is "The next customer might have 4, and the next might have 7." And since they will actually be the ones changing the code, you don't know for sure how proficient they are with their cut, paste, concatenate or bingo skills. But what can you possibly do, the connections are hard coded? Right?
Wrong. The compiler needs a connection string but the reality is it doesn't care how it gets it. Which means ... you can use a Variable for it.
Set vInstance = 'Snowflake_qlik.us-east-1.snowflakecomputing.com (qlikpe_qlikservice)';
LIB CONNECT TO '$(vInstance )';
I hear you out there shouting "But that doesn't achieve anything other than making the code more complicated!" But you didn't let me finish. Of course I'm not going to do my Set vDesc on a line by itself, I'm going to LOOP through the connections and set the variable vDesc inside the loop like this.
SnowflakeInstances:
Load * Inline [
SFConx, SFType, SFInstance
SF_AT, NotLive, 'Snowflake_attunity_partner.xxxxx.snowflakecomputing.com '
SF_PE, Live, 'Snowflake_qlik.xxxxx.snowflakecomputing.com'
];
Let vNumRows = NoOfRows('SnowflakeInstances')-1;
For i = 0 to $(vNumRows)
Let vConx = peek ('SFConx',$(i),'SnowflakeInstances');
Let vInstance = peek ('SFInstance',$(i),'SnowflakeInstances');
Let vType = peek ('SFType', $(i), 'SnowflakeInstances');
LIB CONNECT TO '$(vInstance )';
Yeah the customer with 2 different Snowflake instances is covered.
Yeah the customer with 3 different Snowflake instances is covered.
Yeah the customer with ... you get the picture. All customers is covered. 1 to N instances is all the same to you.
Let's say I am reading a Table called SESSIONS. My table header takes advantage of that vConx variable:
[$(vConx)_SESSIONS]:
And when I want to Store the data into a QVD so I can handle incremental loads, that variable is handy again:
Store [$(vConx)_SESSIONS] into [$(vQVDLocation)/$(vConx)_SESSIONS.qvd] (qvd);
What in the world is the vType variable used for? Does Live or NotLive even make sense?
It does in my particular use case. I have some very large instances that I need to read from for "my" testing, that I only have limited access to. Thus I need to connect Live one time, but I want to read the data from my QVD file when it's flagged as NotLive the rest of the time. A simple little IF THEN ELSE and I'm all set. I either choose to read the data from the server or simply read it from the QVD with no incremental loading.
IF (vType = 'Live') Then
If you were observant you will have noticed that I used a variable for the storage location as well as the QVD filename. Because you know the customers that receive this application will all create different Connection paths for the QVD files so I simply let them define it in the beginning of the code rather than requiring them to change it in every single section of the load script. Plus I'm cooking with gas when I deploy to SaaS and my storage is a different space and not just a file location.
I like to variabilize as much of my code as possible. It's either because I'm lazy -OR- because I'm dedicated to easily modifiable code -OR- both. 😀
