Sometimes just because you can, doesn’t mean you should. Although sometimes it pays to break that rule, in many cases it is the one rule that we pass on to our kids that we most hope they follow. And so, it follows that maybe it was the one rule our parents passed on that they hoped we would follow too. Simplified, I think it was called “The Lemming Rule.”
Information Design Tool (IDT) is a fantastic enhancement by SAP for designing universes. I am a proponent of using it. But when IDT came out, I was a little surprised at all the hype given to one of the features – multi-source connections. The idea is that IDT gives us the ability to bring SQL Server data into the same universe as Oracle data, for example.
I didn’t see the fuss of multi-connections. I could only see the downsides. After all, performance would dictate single connection with data warehousing from multiple sources.
And there is a risk that people will set all data foundations as multi-source because single-source connected data foundations cannot be changed to multi-source connections after creation. But this is potentially harmful performance in unnecessarily adding in the federation service use. I hear my parents quietly whispering the lemming song…
I do, of course, use the multi-source for SAS and SAP BW connectivity, but that is its own use-case.
Certainly, there is real-time querying. But in many cases the merging of data providers in a document means that you could get what you want from two separate universes and not have to deal with the maintenance and performance issues of combining sources. If you combined them at the universe level, although perhaps gaining some ease of use for developers, having to keep up indexing and being limited on SQL are big downsides.
Today I came across a real use scenario that changed my mind.
My client needs to run a real-time minus query across multiple data sources. For this scenario, this is the perfect solution. You cannot do a combined query in Webi unless the data is in the same universe. Although a union is reproducible through merged data providers, minus queries aren’t. Further, the results of the minus query can then be used in the query filter of other queries in the document to get the exact results needed. No management of indexing is required since the sources are not joined.
I can see this case as useful in many environments. The main dimensions were the invoice number and date which exists in multiple sources for different reasons. The same kind of thing would be seen with work tasks and customer accounts and more.
I’m happy to finally have a use-case for this purpose. Although lemmings are cute, my parents whisper is not so much. In classes I’ve taught we’ve had discussions around this topic that I put out to the group up until now without a hit. If you have a great use-case you’ve seen for multi-source connections, please share it in the comments below. Maybe I’ll start using multi-connections in IDT more often.