I have in hands a few Azure SQL databases in which I want to perform analytics and visualization.
My problem is that the data I want to use for visualization is stored in different databases, in the same server. Let's say we have the following structure:
Company-Azure-Server.net
Clients_DB
Table_Companies
Table_Citizens
Followers_DB
Table_FollowRelationship
I want to analyse the relationship between Citizens, Companies and their FollowRelationship.
This architechture made no sense to me in a data analysis perspective, since it is not taking advantage of the relational nature of the relational database.
Nevertheless, I was told they have a strong preference in keeping it this way for performance sake of accessing it with microservices, rather than combining it all in a single database.
Now, I know that with SQL Server, cross-database queries would be no problem, With Azure SQL though, it becomes a lot more complicated.
My question then is:
What data analysis and visualization tools ate there that are able to perform these cross-database queries with Azure SQL? Preferably open source, since we are a startup
You can use Azure SQL Elastic Pools, add your Azure SQL databases to the elastic pool, then you can run elastic query to query data cross-database.
The elastic query feature (in preview) enables you to run a Transact-SQL query that spans multiple databases in Azure SQL Database. It allows you to perform cross-database queries to access remote tables, and to connect Microsoft and third-party tools (Excel, Power BI, Tableau, etc.) to query across data tiers with multiple databases.
I suggest you to use SQL Server Management Studio(SSMS) to run this cross-database elastic query. It's free.
SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure. Use SSMS to access, configure, manage, administer, and develop all components of SQL Server, Azure SQL Database, and SQL Data Warehouse. SSMS provides a single comprehensive utility that combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server for developers and database administrators of all skill levels.
Reference tutorial: Get started with cross-database queries (vertical partitioning) (preview)
Hope this helps.