Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Calculated Member for last-child in non-time dimensions?

Tags:

sql

olap

ssas

mdx

In a SSAS cube, how do I create measures that are aggregated as LastChild for a non-time dimension?

The source data has many versions of the same business record on any given day. The time dimension has a granularity of DATE, not seconds & milliseconds.

The fact records have a timestamp and an incremental (identity) primary key. In effect, what I want is to calculate a measure as being the last value for all edits on a given date.

The options I've seen so far fall into one of two categories:

  • Produce a time dimension that goes down to seconds. This would result in a very large and inefficient time dimension.

OR

  • Hide the measures and replace them with calculated measures that look up the last value for any given date based on the primary key. This is cumbersome and less efficient.

Is there a sweet spot or alternative technique to solve this problem?

The natural hierarchy of the data is:

  1. Business Key
  2. Record Timestamp (links to TIME dimension)
  3. Surrogate Key
like image 697
Mark Avatar asked Nov 14 '11 11:11

Mark


People also ask

What is calculated member in MDX?

In Multidimensional Expressions (MDX), a calculated member is a member that is resolved by calculating an MDX expression to return a value. This innocuous definition covers an incredible amount of ground.

What is a calculated member?

Calculated members are members of a dimension or a measure group that are defined based on a combination of cube data, arithmetic operators, numbers, and functions. For example, you can create a calculated member that calculates the sum of two physical measures in the cube.


1 Answers

No, you absolutely cant create a latChild attribute without a time dimension:

enter image description here

what you can do is create a query on your DSV to give you the last child value and use it to create a measure group. Example:

Script:

create table dim(
id int not null primary key,
name varchar(50))
insert into dim values (1,'one'),(2,'two')

create table fact(
id int not null primary key,
dimId int not null,
value numeric (12,3),
constraint fk_fact_dim foreign key(dimId) references dim(id))

insert into fact values (1,1,5)
insert into fact values (2,1,3)

insert into fact values (3,2,10)
insert into fact values (4,2,20)

very simple script that creates a dim and a fact table. Here is a simple select and after the result I think you want, so 3 and 20 would be the last values based on the Dimension's ID:

enter image description here

to build that is very simple, just right click on your DSV and add a new named query and inform the query to create your last value. In my case is:

select dimId, 
(select top 1 value from fact where dimId=F.dimId order by id desc) as lastValue
from fact F
group by dimId 

create a logical primary key and link it to the dimension table: enter image description here

and on your cube, right click any blank area, select "show all tables" and add your new table.

And on your cube create a new measure as "no aggregations" because you have already aggregated your query:

enter image description here

Result:

enter image description here

like image 103
Diego Avatar answered Oct 27 '22 06:10

Diego