The database has data on user sessions. For sessions there is a start date and an end date.

I need to build a cube and query on this cube to get daily statistics from how many users the session ended on that day. And it is necessary to count only those who at 00:00:00 of the current date had an open session, and at 00:00:00 the next day already do not.

Similarly, you need to be able to receive data by week, month, year (When the session is open at the beginning of the relevant period, and closed at the end of the period).

There is such an mdx request:

SELECT { [Measures].[Count] } ON COLUMNS, { ( [Date Off].[Year].CHILDREN,[Date Off].[Month Name].CHILDREN,[Date Off].[Day Of Month].CHILDREN ) } ON ROWS FROM [MyCube] WHERE ([Date Off].[Filter Value].&[20160818]:[Date Off].[Filter Value].&[20160918])

On it, I can get the number of completed sessions by day. But the problem is that the session can begin and end in one day and also fall into this sample. How can I refine the request to take into account only the sessions that started before the current day? And how else to limit so as not to take into account users who have opened a new session at the end of the day?

To do this, there are measurements in the cube with the dates of the beginning of the sessions and the dates of the beginning of the next session, but how can I apply them here, I can not think of

    1 answer 1

    In the FROM design, you can specify filters.

    Here is a piece of my query:

     FROM (SELECT ( STRTOSET("[Сети].[Наименование].&[4307]", CONSTRAINED) ) ON COLUMNS FROM ( SELECT (STRTOMEMBER("[Дата].[Г-М].[Месяц].&[201610]", CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET("[Торговые точки].[Регион].&[39]", CONSTRAINED) ) ON COLUMNS FROM [Retail Analytics])