10 Replies Latest reply: Aug 16, 2018 8:20 AM by Tyler Tollin RSS

    Tables: Dimensions and Measures

    Tyler Tollin

      I'm running into a consistent problem when trying to create table objects that I think is caused by a misunderstanding on my part about how tables use dimensions and measures.

       

      If I have a table that should display all records from the data model respective to the user's filters, my understanding of the best case scenario is to have a PK as a dimension and place the rest of the columns in as ="" measures. However, my current data set does not have a PK, it is just a collection of rows and columns. My first thought was to simply add every field as a dimension, but this resulted in the columns displaying mostly blank records except for the fields that have unused data points. This throws my count expressions off.

       

      My second thought was to try to parcel out what I could use to group data, but I am consistently coming up with issues where data is appearing as null where it shouldn't.

       

      Is there documentation on this, or could someone help me with an explanation? This seems like it should be simple, but I'm spending more time trying to deconflict tables like this than I should be.

        • Re: Tables: Dimensions and Measures
          Lisa Phillips

          Hi Tyler,

           

          You should be able to create a table with only dimensions to see all your raw data.

           

          The data will be related at row level in each table that is loaded.

           

          When you are counting you can count Distinct values and also put conditions in to not count nulls.

           

          The underlying data model is very important to ensuring that you get the right answer to your questions.

           

          Regards,

          Lisa

            • Re: Tables: Dimensions and Measures
              Tyler Tollin

              Thanks for the reply, Lisa.

               

              What I've found is that when I add a field as a dimension from a dimension table, many times I have records created to display items from that dim table that perhaps are not being used in other records. This doesn't fit my needs, but maybe there is a way around this?

                • Re: Tables: Dimensions and Measures
                  Lisa Phillips

                  Can you give an example please ?

                    • Re: Tables: Dimensions and Measures
                      Tyler Tollin

                      Sure.

                      In this instance I have a collection of data that contains a single field for an employee's code which uniquely identifies them. Their name, location, or manager is not in this table. To get this information, I bring in an employee table with all employees, their location, manager, and some other things. The relationship between the code from table A and the code from table B is correct, however there are other employees with codes that do not appear in table A. If I bring the employee code field in as a dimension, all possible codes appear on my front-end, whether they appear in table A or not. This bloats my record count with records which are mostly empty except for emp code, emp name, emp location, and emp manager, for instance.

                       

                      Does this help?

                        • Re: Tables: Dimensions and Measures
                          Marcus Sommer

                          Just filter the second load to the available values from the first one, maybe in this way:

                           

                          facts:

                          load EmployeeID, ... from tableA;

                           

                          dim:

                          load EmployeeID, ... from tableB where exists(EmployeeID);

                           

                          - Marcus

                            • Re: Tables: Dimensions and Measures
                              Tyler Tollin

                              Thanks Marcus, I like this option.

                               

                              Is it possible do a where with alias fields? One of my tables has the employee ID as another name. I've also tried

                               

                              Where

                                   Exists("empID") or

                                   Exists("otherEmpID");

                                • Re: Tables: Dimensions and Measures
                                  Marcus Sommer

                                  I'm not absolutely sure what you mean with alias fields but exists() has also an optional second parameter and a statement could look like:

                                   

                                  Where Exists("empID", "otherEmpID");

                                   

                                  or maybe with an expression:

                                   

                                  Where Exists("empID", left(AnyField, 5));

                                   

                                  By loading from qvd's or resident-tables and larger datasets (in the above mentioned case it's not related to the fact- else the dim-table which is probably not very large) it's useful to prepare the data in a way that the exists() worked with a single-parameter because it keeps an load optimized.

                                   

                                  - Marcus

                                    • Re: Tables: Dimensions and Measures
                                      Tyler Tollin

                                      So if I have...

                                       

                                      Sales:
                                      Load
                                          Sale_ID,
                                          Prod_ID,
                                          Cust_ID,
                                          Emp_ID as Emp_Num
                                      From tableX;
                                      

                                       

                                      and

                                       

                                      Employee:
                                      Load
                                          Emp_ID as Emp_Num,
                                          Emp_F_Name,
                                          Emp_L_Name
                                      From tableY
                                      WHERE EXISTS(Emp_Num);
                                      

                                       

                                      ...this doesn't appear to work, but perhaps I'm doing something incorrectly. In this instance, I can't really get around using an AS.

                                        • Re: Tables: Dimensions and Measures
                                          Marcus Sommer

                                          In this case you need to use the exists() with two parameters like above mentioned maybe with:

                                           

                                          ...

                                          WHERE EXISTS(Emp_Num, Emp_ID);

                                          ...

                                           

                                          But if possible it's better to use exists() only with one parameter because it's easier and more performant. If you want to rename this field you could do it directly. I mean:

                                           

                                          Sales: 

                                          Load 

                                              Sale_ID, 

                                              Prod_ID, 

                                              Cust_ID, 

                                              Emp_ID 

                                          From tableX; 

                                           

                                          Employee: 

                                          Load 

                                              Emp_ID, 

                                              Emp_F_Name, 

                                              Emp_L_Name 

                                          From tableY 

                                          WHERE EXISTS(Emp_ID); 

                                           

                                          rename fields Emp_ID to Emp_Num;

                                           

                                          - Marcus