Geek asks and answers

Capturing the first or last value for a custom time period

Firstly, very new to SSAS. But what I would like to do seems rather trivial (I hope).

I have transactional data that arise on the second. And I created two time dimensions. One measurement is based on a standard time measurement using the time measurement wizard. Another is the time dimension that was created by my own script, where I have a record for every second of the day.

I linked my fact table to my dimensions and correctly deployed the cube. In my factual data, one attribute is price. Now, using min and max, I was able to get the highest price and lowest price that occurred over a certain period of time. What I would like to receive is the first and last price that has occurred over a certain period of time. I tried using the "first value" and "last value", but the return value is incorrect, it seems to be some kind of aggregation.

For example. If my prices for 1 minute were:

1233, 1233.85, 1300.1250

First you need to return, 1233 and last should return 1250. This should work in all periods of time.

To add, for my custom time dimension, I tried to set its type to "Time", but the results remained the same. I appreciate any suggestions. Basically I want to get the first value of the base and last value.

An example request that adds me part of the path:

SELECT NON EMPTY 
     { [Measures].[High]
     , [Measures].[Low]
     , [Measures].[Price Count]
     , [Measures].[Price]
     } ON COLUMNS
     , NON EMPTY
     { [Dim Date].[Year -  Week -  Date].[Date].ALLMEMBERS
     * HEAD( [Dim Time].[Hour - Second].[Second].ALLMEMBERS )
     } ON ROWS 
FROM [Cube]

enter image description here

Note that the price column has several entries. I need the first entry from the inside, I do not need the actual amount.

+3
source share
1 answer

Try to create a new measure that gets the price for the first second of the time measurement. For example, if you want the first second of the day, then you do not need this in a cross-connection in rows, but you can get the first second of the [Hour - Second] hierarchy inside the calculated measure:

with
    member [Measures].[First Price] as
        (head(descendants([Dim Time].[Hour - Second].CurrentMember, Second)), [Measures].[Price])
select
    non empty {
        [Measures].[High],
        [Measures].[Low],
        [Measures].[Price Count],
        [Measures].[Price] 
    } on columns,   
    {
        [Dim Date].[Year -  Week -  Date].[Date].ALLMEMBERS
    } on rows
from
    [Cube]

, , . , - :

    non empty {
        [Dim Date].[Year -  Week -  Date].[Date].ALLMEMBERS * [Dim Time].[Hour - Second].[Hour]
    } on rows
0

More articles:


All Articles