setting up one data cube for multiple metric sets?

#1

Hello,


I’m trying to understand the best way to provide access to our database without the data connector being logged in as a user with full DBA privileges especially since under no circumstance do we want users updating data.

I’ve got a database user that has more limited access, user dataX. This user can query any of our tables in our database including their own schema, but the primary schema that we need to query does not show up in my data connecter (which is also good) so I’m having to resort to manual SQL queries for my data cubes (which is fine with me).

Thus far I have one dashboard with two tables (from two metric sets) which pull similar types of data about members. Right now each metric set uses each own data cube which does not seem scalable considering how many dashboards and metric sets we will be building over time, so I’m thinking there must be a better way to combine this into one cube since they cross similar tables but since one table has member(s) that DOES NOT HAVE and needs a form 8300 and the other shows the info about the members that HAVE a form 8300. Combining the data cubes into one element would also goes in line with the ERD of our data.


This is what is in place now:

DASHBOARD 1.0

Table 1 > metric set 1 > data cube 1 (manual SQL 1)

Table 2> metric set 2 > data cube 2 (manual SQL 2)


I cannot seem to figure out how to get this to work:

DASHBOARD 1.0

Table 1 > metric set 1 > data cube A with (manual SQL 1) and (manual SQL2)

Table 2> metric set 2 > data cube A with (manual SQL 1) and (manual SQL2)


Maybe I’m going about this the wrong way? Any input or advice is much appreciated.



#2

Image title
1CgqDfg4ReGByYEok9wZ_references.jpg337×560




#3

This is what I have currently. I'd like to have just one FORM_8300 datacube.

#4

In general, you don't need more than Read access on the database to query the data, though the specifics may change depending on the database you are using.


I am a bit confused regarding the query not showing up in the data connector, but would need some more information to understand what is going on there. However, assuming that you do need two separate manual selects, you can always join or union them on the same data cube.


If I understand your situation, you probably have a column indicating whether the form was filed or is still needed. You can simply use this column for your slicer and filter the metric set according to your needs.


I will gladly help you more here, but would need some additional details. Alternatively, if you don't want to share those details publicly, you can message me directly or contact Support.

#5

Thanks Elia, I sent an email to support.