Forum Discussion
Modelling elastic document to relational tables
I'm trying to get PowerBI to play nicely with Elastic, and for that I have a relatively simple data structure. However, as Elastic revolves around having all information regarding an entity/document in the document it self, it pose some difficulties trying to split it into something PowerBI can work with. My document contains these structures:
Key: value - easy enough
Key: Record - easy enough if the record only contains values
Key: List<values> - this one is the one posing me problems. it's a 1:N type of entity, which would normally be modelled relationally.
I have multiple lists with values (even some containing more lists), but I can't seem to represent this relationship in PowerBI such that it can understand it. Any help regarding this?
Edit: In short, is it possible to take one data source and transform it into multiple tables.
I'm beginning to think the best idea is to develop a bridge between elastic and PowerBI in .NET which transforms the data into normalized relational data.
Best Regards
Hi DennisB,
Edited:
If only for this question "In short, is it possible to take one data source and transform it into multiple tables."
Yes this is possible to achieve in Power BI desktop Query Editor.
Please check the article below first regarding common query tasks under Query Editor:
Common query tasks in Power BI Desktop
In addition, could you please share some details regarding the data source here, for the 1:n list, if I understand it clearly, when under Query Editor, we could be able to expand the "n" list.
By the way, if Elastic stands for Elasticsearch, then please check the article below and see if it would help:
Querying Elasticsearch with PowerBI
Please reply back if you need any further assistance.
Regards
2 Replies
I'm trying to leverage the advantages of DocumentDB / Elastic / NoSQL for retrieving big data and to visualize it. I want to use PowerBI to do that, which is pretty good, however, I have no clue how to model a document which has a 1:N nested data field. E.g.
{
name: string,
age: int
children: [ { name: string }... ]
}
In a normal case, you would flatten the table by expanding the nested values and joining them, but how does one do that when it's 1:N / A list. Is there a way to maybe extract that into it's own table?I've been thinking about making a bridge which translates a document into data tables, but that feels like an incorrect way to go, and further proves some complications with regards to how many endpoints and queries there should be made.
I can't help but think this is a solved issue, as many places analyse and visualize large amounts of data stored in no sql. The alternative is a normalized relational database, but having millions and millions of entries in that which you analyze also seems incorrect when nosql is tuned for these scenarios.
How is this dealt with?
v-micsh-msftMicrosoft Employee
Hi DennisB,
Edited:
If only for this question "In short, is it possible to take one data source and transform it into multiple tables."
Yes this is possible to achieve in Power BI desktop Query Editor.
Please check the article below first regarding common query tasks under Query Editor:
Common query tasks in Power BI Desktop
In addition, could you please share some details regarding the data source here, for the 1:n list, if I understand it clearly, when under Query Editor, we could be able to expand the "n" list.
By the way, if Elastic stands for Elasticsearch, then please check the article below and see if it would help:
Querying Elasticsearch with PowerBI
Please reply back if you need any further assistance.
Regards