6 Replies Latest reply on May 22, 2020 1:23 PM by Andres Fernandez

    how to tell if column is measure or dimension?

    Andres Fernandez

      Hi

       

      Once you get the dataTable from calling worksheet.getUnderlyingDataAsync, and you get its columns property, how do you know if a column is a dimension or a measure?

       

      Thanks

      -Andres F

        • 1. Re: how to tell if column is measure or dimension?
          Keshia Rose

          Hi Andres,

           

          You will need to get the data source directly. Once you get the data source, you can find fields and each field has a role property that will tell you if it's a dimension or a measure.

           

          Take care,

          Keshia

          • 2. Re: how to tell if column is measure or dimension?
            Andres Fernandez

            Thanks for your reply, Keisha

             

            how could I then match the column from getUnderlyingData to the data source field from getDataSourcesAsync?

             

            It can't be by comparing the column name to the field name, can it? because the column name may include operations or aggregations

            e.g.

                "MONTH(Order date)" != "Order date"

                "SUM(Sales)" != "Sales"

             

            Besides, what if there's the same field name in two different data sources, which one would I know my column refers to?

            • 3. Re: how to tell if column is measure or dimension?
              Keshia Rose

              Hi Andres,

               

              Hmm fair point, we currently do not have any way to tie the fields you see on the worksheet back to the data source. I've made a note of this and perhaps it is something we will add in the future but it is currently not available. Can you tell me more about your use case? Perhaps there is a possible workaround.

               

              Take care,

              Keshia

              • 4. Re: how to tell if column is measure or dimension?
                Andres Fernandez

                Thank you!

                 

                I'd like to show the user a list of all the dimensions and measures of the dashboard (like the dimensions and measures that encompass all the underlying data), then have him select at least 1 dimension and 1 measure, and then get the facts relevant to the selected dimensions and measures only (like getting all the underlying data but filtered by the selection)

                • 5. Re: how to tell if column is measure or dimension?
                  Keshia Rose

                  Hi Andres,

                   

                  In that case you may be better off just grabbing the data sources from the worksheets and getting the fields and underlying data from there. The tricky bit is getting the filters though. You might be able to get away with matching the column names with the worksheet field names by stripping away the parentheses but that seems apt for error.

                   

                  Take care,

                  Keshia

                  • 6. Re: how to tell if column is measure or dimension?
                    Andres Fernandez

                    Thanks Keisha,

                     

                    Yes, we're using regular expressions to match the column names (from getUnderlyingData) to the field names (from getDataSources) and it seems to be working ok with the data from several sample dashboards. It's good enough to get a list of dimensions and measures.

                     

                    What worries me now is the retrieval of the facts only relevant to the user-selected dimensions and measures. Could you help me with some guidance on how should I proceed?

                     

                    The way I see it there are two scenarios:

                    1. the selected dimensions and measures belong to the same data source. In which case: is there a way to get the facts from a data source filtered by a subset of its dimensions and measures?
                    2. the selected dimensions and measures belong to different data sources. In which case I don't know what could possibly I do to get the relevant facts

                     

                    Or maybe... is there a way I could create a worksheet programmatically, set its dimensions and measures (based on the user selection), and then call its getUnderlyingData ? this would solve both scenarios