Single Schema for different DBs

[Former Member]

Is it possible in IDT to have multiple dynamically allocated connections for several identical database schemas ?

Essentially, what we would like to do is to have a single Data Foundation with only one set of tables, but with the ability to run the same SQL query several times, against different databases, which have the same structure. For example, a query to extract data from different standard build warehouses, each for a different client. Another example would be to compare data between several identically built Test and Prod environments, to see if there are any data differences.

This would have the same effect as using data from multiple identically built universes in Webi via Merge Dimensions, but without the need to have multiple sets of tables added into IDT Data Foundation  each time a new client or environment is required.

Is such dynamically allocated connectivity without the need for additional sets of cloned tables possible in IDT ?

[Former Member · Accepted solution]

It is possible in IDT. You can define override connections in IDT for difderent user groups using Data Security Profiles. Please take a look at Securing universes in the below link - http://scn.sap.com/docs/DOC-8461

[Former Member · Accepted solution]

Hello Mohanraj,

Thanks for the link you shared.

We already use connection overrides within our universes. What we want to do now as we discussed, is to be able to dynamically change the connections. Let’s say we have three identical database systems, A, B and C with a single universe, three matching user groups, three connections and three overrides. Each of these groups only accesses their own database, which is fine.

However, what we want to do is to be able to create a fourth super user group, or an administrator, to be able to say: OK, so we have three identical databases, and I want to see the grand total of the account balances they hold, A+B+C. So I want to be able to automatically run the same query 3 times against systems A, then B, then C, without actually having to do it manually three times, or changing connections, or assigning different groups, I am looking for a fully automated process within IDT.

Also I looked into Indexed Views in SQL Server and I am coming across sources stating that we cannot build Indexed Views in SQL Server across multiple databases, so unfortunately this will not be of much use to us. Please let me know if you have any other suggestions?

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/22be6ec9-4882-46f0-b6f5-3335a48438af/can-a-...

http://www.brentozar.com/archive/2013/11/what-you-can-and-cant-do-with-indexed-views/

[Former Member · Accepted solution]

How about the below approach?

Assuming that you have these databases sitting in separate servers,

- Setup DB links using Linked Server in one of the server

- Create database views to merge the data from all three databases

- Use Override Tables option for the Administrator groups to point to the view

[Former Member]

The DBs are hosted on the same SQL server

[Former Member]

How about creating a view that brings data from these databases together and used as the override for administrators group?

[Former Member]

You can certainly do it in the UDT using connection management functionality within the manage security section. You may be able to apply the same logic to the IDT, but I've never checked