Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Connecting Mongo with PowerBi without using a flattened database

Hi, I'm an employee at a thin film fabrication firm. The tldr overall question of mine is whether there's a way to handle deeply nested /hierarchical/ragged data directly in PowerBi. I've tried a few free widgets for hierarchical data but our data seems too complicated for the widgets. Especially since there is no one schema that truly represents the data, as different clients may require machinery with different combinations of components.

To give some background, I'm working with a Mongo DB instance which contains deeply nested hierarchical data. I've provided an example of the data with the field and values randomized below. So far, to connect to PowerBi I've used the Atlas SQL Bi Connector for Mongo, but I've had to make a "copy" database which contains the flattened version of my original database.

The problem with this approach is that there is this intermediary step of flattening the data every time we update the database. I'm in touch with Mongo support about this as well. 
Example:

 

 

{
  "_id": {
    "$oid": "f1e2d3c4b5a697887766554433221100"
  },
  "jeff": "abcde",
  "sprocket": [
    {
      "wibble": "12345678-90ab-cdef-1234-567890abcdef",
      "foo": false,
      "bar": "xyzzy",
      "qux": "plugh"
    }
  ],
  "flibber": [],
  "gizmo": [],
  "wobble": [
    {
      "blorp": "qwerty",
      "snarf": [
        {
          "spaz": "asdfgh",
          "whiz": "zxcvbn"
        }
      ],
      "thingamajig": [],
      "doodad": [
        {
          "foobar": "foobar",
          "frobnicate": "frobnicate",
          "bloop": "bloop",
          "blarp": "blarp",
          "widget": "widget",
          "doohickey": [
            [
              {
                "whatzit": "whatzit",
                "splork": "splork",
                "zibble": [
                  "zibble"
                ],
                "frob": "frob",
                "zork": false,
                "gorp": false
              },
              {
                "whatzit": "whatzit",
                "splork": "splork",
                "zibble": [
                  "zibble"
                ],
                "frob": "frob",
                "zork": false,
                "gorp": false
              }
            ]
          ],
          "bliz": [
            {
              "yarp": "yarp",
              "glorp": "glorp"
            }
          ],
          "zoink": true,
          "flurb": [
            {
              "snizzle": "snizzle",
              "crizzle": false,
              "yibble": "yibble"
            },
            {
              "snizzle": "snizzle",
              "crizzle": false,
              "yibble": "yibble"
            }
          ]
        }
      ]
    }
  ]
}

 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    I'm sorry I may not understand what you mean, could you provide sample data (not code) of numerical type and what your expected result is? 

    Here are some suggestions I can offer you:
    For dealing with complex structural hierarchies in your data, Power BI can handle a certain level of hierarchy with its built-in grouping and drill-down functions. 
    Use grouping and binning in Power BI Desktop - Power BI | Microsoft Learn
    Drill mode in the Power BI service - Power BI | Microsoft Learn


    You can also filter the entire hierarchy by putting all the fields it contains into the slicer. For example:

    If your hierarchy is too complex, you can try using this custom visual:

    It allows you to filter by searching directly for keywords

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    The goal is to use slicers to create a search tool.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous ,

    I'm sorry I may not understand what you mean, could you provide sample data (not code) of numerical type and what your expected result is? 

    Here are some suggestions I can offer you:
    For dealing with complex structural hierarchies in your data, Power BI can handle a certain level of hierarchy with its built-in grouping and drill-down functions. 
    Use grouping and binning in Power BI Desktop - Power BI | Microsoft Learn
    Drill mode in the Power BI service - Power BI | Microsoft Learn


    You can also filter the entire hierarchy by putting all the fields it contains into the slicer. For example:

    If your hierarchy is too complex, you can try using this custom visual:

    It allows you to filter by searching directly for keywords

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.