0

We have some data inside a table (Dimension) with historical values.

Like this (Small example) enter image description here

ProductId is our Primary Key (and then is unique)

Code is our Business Key

Color and Type are our historical values

In Analysis Services (Tabular mode), our users want to build a report on that values. Client usage Could be:

(1) If they only want to see the code ('CAR' in our example) the result would be:

enter image description here

(2) If they want to see the code and the Color:

enter image description here

Same for all the attributes that we can have and all the combinations.

Do you know how to solve this? Can we add some logic in a calculated attribute

Thank you, Arnaud

Arnaud Gastelblum
  • 312
  • 1
  • 11

1 Answers1

1

In essence, you want to aggregate by date? So, for any set of attributes you put in your pivot table, you want to show the earliest ValidFrom date and the latest ValidTo date that applies?

To accomplish this in SSAS Tabular, import the table and hide the columns ValidFrom & ValidTo. (To hide a column, right click it in Visual Studio and select Hide from Client Tools.)

Then, create 2 measures. For example:

Valid From := MIN([ValidFrom])
Valid To := MAX([ValidTo])

Note the extra space in the names to distinguish them from the column names. You could also call them something completely different. (E.g. Earliest Valid From Date)

When people connect to your cube, people will use these 2 measures rather than the columns from the original table. (They won't even see the columns because you've hidden them.)

If their pivot table includes all the attributes above (Product ID, Code, Color, Type), then the table will look exactly like your original table. If they only show Code, then your table will look like your (1). If they only show Code & Color, then your table will look like (2).

Leonard
  • 2,558
  • 2
  • 15
  • 29