Get active person by selecting a date

Unknown

Good afternoon,
probably someone has a good instruction guide or example on how to create cubes to resolve this:

 

I have a mssql table "person" and "person-activity". In the table "person" I have the name, code of the person
and other anagraphic data of  the person. In the table "person-activity" I have the code of the person and the date of
the beginning of the activity and the end date of the activity.

 

In Board I would like to select a day and see all the active person in a dataview.

 

Example 1:

 

a) I select the 20.01.2018
b) table person-activity:
    person a - date of entry = 10.01.2018, end-date = 21.01.2018
    person b - date of entry = 01.05.2018, end-date = 20.05.2018
c) the result = person a

 

Example 2:

 

a) I select the 22.01.2018
b) table person-activity:
    person a - date of entry = 10.01.2018, end-date = 21.01.2018
    person b - date of entry = 01.05.2018, end-date = 20.05.2018
c) the result = any person

 

Is this possible or do I have to prepare the data before the import? For example an import day by day with all the active person?

 

Thanks for your help,
Egon.

Pietro Di Cino

Hi Egon Santoni, 

a solution can be a simple sql datareader linked to a cube structured by day-person.

When you read the data, go to manual mode and try this query (in [...] please insert the fields of your table):

 

select dateadd(day, [starting_date], number), [person_id], '1'

from master..spt_values n with (nolock), person-activity

where type='p' and and number between 0 and datediff([starting_date], [ending_date]

 

With this solution I think that you will write into your cube a 1 per each day of "presence" of the single person.

 

Feel free to comment with your solution when it run.

Regards,

Previous Member

Hi Egon Santoni, one option to perform the logic inside BOARD is to load both tables as they are and perform the processing via dataflow action of a procedure.

 

For this you need 4 cubes:

A Begin Date (Date type cube dim by Person) - populate from SQL

B End Date (Date type cube dim by Person) - populate from SQL

C Day (Date type cube dim by day only) - populate via dataflow using Day = day() function to hold date values for every day

D Active Flag (Integer cube by Day by Person)

 

To populate D, you need IF statement =if(and(c=>a,c=<b),1,0)

 

You can then multiply any anagraphic data by this ACTIVE flag mapping to have only values shown in days the Person was active, or even use the Active Flag as a filter in the layout depending on what you are trying to do.

 

I hope it helps!

 

Thanks,

Audrey