Dynamically loading variables from multiple different databases

[Anonymous]

Hi all,

we need to plot a few variables out of a large dataset from different databases in Power BI. At the moment, we use an SQL statement which performs a join on different tables and selects the variables which we want to plot. However, many times during the analysis process, another variable from the dataset is needed which is not mentioned in the SQL statement. This variable(s) changes based on a/the process step and is therefore not fetched in the first SQL statement. Therefore, at this point we have two ways to proceed:

1. Rewrite the SQL statement, run it and then plot the new variable.
2. Load all the variables and select the ones we need as we go (just like in Import mode)

Option 1 is a bit cumbersome as we do not want to rewrite the query each time and interrupt our analysis process.

Option 2 is a complete but a non-optimal (non-performant) solution for us as we do not want to load the entire dataset for 4-5 variables which will actually be analyzed.

Therefore, we would like to ask: Is there a way to dynamically load the variables from the dataset (different dbs) without interrupting the analysis process?

Thank you in advance for your help!

[Anonymous]

Hi,

Thank you for your response. Yes you are right, by variables (in Power BI), we mean the columns in our table. Here is a sample example:

For the sake of this example, we consider three tables A, B & C.
SQL statement:

SELECT
 a.id,
 b.id,
 100*a.length*b.width*c.height volume,
 c.height_offset offset

FROM
  A a,
  B b,
  C c

WHERE
 a.id = b.id
 AND b.id = c.id
 AND c.id = a.id

When we run this query, we get 4 columns in our Power BI table. While plotting this information, we may realize that we need the "thickness" from the table A. Therefore, we rewrite the SQL statement to fetch "a.thickness" from the database which is quite cumbersome. We would like to know if there is any other way, such as an input parameter in our dashboard, which takes input from a user to simplify the process of adjusting the SQL statement (especially for a user who is not familiar with the SQL statements).

Thank you for your support!

[parry2k]

@Anonymous of course #1 is not a scalable solution, no one wants to go back, change SQL, and then update the model in Power BI.

Bit confusing part is when you say "variables", are you adding extra columns to the SQL statement? If yes, then that is where the problem is? It means your data is pivoted, for a more scalable solution, you need to bring unpivoted data. 

I think at this point, it will be easier if you share some sample data with an example of what happens when you add a new variable and it will help to provide a better solution.