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

Question

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

Answer 1

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

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:

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:

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:

Result: