Tables: Dimensions and Measures

[ttollin11]

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.

[Lisa_P]

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

[ttollin11]

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?

[Lisa_P]

Can you give an example please ?

[ttollin11]

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?

[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

[ttollin11]

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");

[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

[ttollin11]

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.

[marcus_sommer · Accepted solution]

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