Alias of dimensions after sql execution but before rendering


#1

Say I have two tables as follow:

create table aggregation (
d_id text not null,
d_os text not null,
m_request int not null,
m_click int not null
);
create table id_mapping (
id text not null,
name text not null
);

Now I have a chart running the sql

select d_id, d_os, sum(m_request), sum(m_click) from aggregation group by d_id, d_os;

The chart is not human readable because the d_id is GUID. What’s worse, I cannot have a name column in the aggregation table because names may have collision so the group by will be incorrect.

As the topic described, can I have some sort of alias (not the same one as Immerse have now) that runs a sql(in my case, select name from id_mapping where id = 'xx' limit 1) or something else to get the alias name of a dimension after the group by sql is executed but before the chart is rendered?

Hope the description is clear to you.


#2

Hi @on99,

Thanks for trying out MapD!

Are you running the latest released version of MapD (3.2.2)? In that release we added the ability to alias custom dimensions and measures (more detail in this blog post here, and doc on custom dimensions here and custom measures here). While its a bit awkward to have to use a custom dimension/measure for a simple column like d_id it should function as a workaround for now.

We are working on the design for a schema editor which will allow you to assign custom aliases to any variable as well as define custom dimensions/measures at the table level (i.e. reusable over multiple dashboards). We don’t have a firm ETA on that yet but it should make what you’re doing even easier. In the meantime the path above should do the trick.

Please let me know if this works for you and to make sure we understood your question properly!

Regards


#3

@darwin Thanks for your reply!

Yes the latest released mapd is running here. Unfortunately, the alias feature is not what I am looking for. What I truly wanted is a look up table(from other tables, from a local csv file, from remote http result, etc), which replaces the column value in dimension columns, not the column name.

Let me elaborate with an example.

aggregation table

d_id d_os m_request m_click
5596E0C1-7CC8-64C7-888E-06656ADF9DAB iOS 108 13
01ED6862-ED2A-1CB9-7046-727A363C48A6 Android 533 37
910ED870-592B-1B4A-CC08-F12189078655 Android 982 146

id_mapping table

id name
5596E0C1-7CC8-64C7-888E-06656ADF9DAB WarCraft
01ED6862-ED2A-1CB9-7046-727A363C48A6 League of Legend
910ED870-592B-1B4A-CC08-F12189078655 WarCraft

In a Immerse text chart, what I select is

dimensions: d_id, d_os
measures:   sum(m_request), sum(m_click)

so the chart will be like:

d_id d_os m_request m_click
5596E0C1-7CC8-64C7-888E-06656ADF9DAB iOS 108 13
01ED6862-ED2A-1CB9-7046-727A363C48A6 Android 533 37
910ED870-592B-1B4A-CC08-F12189078655 Android 982 146

but what I want is:

d_id d_os m_request m_click
WarCraft iOS 108 13
League of Legend Android 533 37
WarCraft Android 982 146

Please noted that both 5596E0C1-7CC8-64C7-888E-06656ADF9DAB and 910ED870-592B-1B4A-CC08-F12189078655 are mapped to the same name WarCraft, so the look up table should not be applied before the select d_id, d_os, sum(m_request), sum(m_click) from aggregation group by d_id, d_os sql is executed otherwise it will generate an incorrect result set (maybe just 2 rows are returned). look up table should only be applied right before the chart is rendered, not before the group by sql is executed.

I think it’s a common use case as data in the database is most likely to be sort of id which is not human readable, so we need to make transform it before the charts are rendered.

Is it clear to you? Thank you so much!


#4

I Based on your query example and data the query would return 3 rows because of os_id column; anyway you could write a view with an inline view aggregating the data and joining back the aggregated result to you lookup table.

Something like that

Select name, os_id, sum_reqs, sum_clicks
From (select d_id,d_os,sum(m_request) sum_reqs, sum(m_click) sum_clicks from aggregation_table
Group by d_id,d_os) agg_table join lookup table on d_id=id

You wouldn’t be able to cross filtering with immerse on aggregation table anyway.

P.s. I am sorry for errors on query or attributes names but I am on phone right now


#5

@aznable Thank you for the solution! It works with the test table schema and data I posted.

However, as for my use case in production, I have many dimension columns needed to be looked up in multiple lookup tables, so the sql or view will be very complicated to maintain, or even not feasible (I am not sure).

Is it reasonable?


#6

@aznable

BTW, the cross filtering feature is very useful as we are not just showing some reports, but setup a BI system for others XD


#7

weel with cross filter problòems i mean, that you cannot filter on fields are not used uotside the inline view, and you are also limited on anaysis because if you need another measuse e.g you want to add a count on your query, you have to modify it and add count on inline view.

about complexity you can manage with prebuilt views on schema.

Anyway there is another and better way to solve you problem

try to write the query this way

select name as d_id,
d_os,
sum(m_request),
sum(m_click)
from aggregation
join id_mapping on (id=d_id)
group by id_mapping.
d_id,name,
d_os;

i just added d_id on group by d_id and an alias on projection to ensure the uniqueness on grouping and preserving the name of column.

to achieve this you would add on every chart the d_id from aggregation table as a dimension to have correct results; the bad thing is that for what i know you can hide any field on immerse at moment.

if you are using others tools like tableau you would be able to hide the filed in one way or another