Conditioned joins for correct results? Rather not....

[SergeS]

Hi,

I am encountering the following issue.

In all of my source tables the actual data as well as the history date for that specific entity is kept. The most important attributes in those tables that tell if a specific record is valid at a certain point in time are 'effectivedate' and 'enddate'.

Endusers need to be able to generate reports with data that is valid at a certain date. So, I defined a prompt in my universe which asks the user to enter a date.
In my dimension objects I am using the entered date to select only those record which are valid at that date. This is working fine in some cases.

However, there are situations in which it isn't working. F.e.:

Imagine I have three tables: Division, Department and Employee. When I am producing a report with these three columns all figures are correct. The problem is showing up when I am using Division and Employee only.
The reason is that the generated SQl doesn't contain my defined condition on the dimension object Department. With respect to Department it is only using the joins Department - Division and Department Employee.

To get the right results it seems I have to created conditioned joins, comparing the effective dates and end dates with the entered date. This is not very elegant (neither is the generated SQL).

Does someone has an idea to handle this issue?

Thanks,

Serge

 

[jimbopalmer]

at the database level, you may wish to make views like current_employees, current_departments and current_divisions with code like


where sysdate between effective_startdate and effective _enddate

this may simplify working with the views in BO I tried to remain child-like, all I acheived was childish.

 

[SergeS]

Hi Jimbo,

I don't exactly know what you mean with ,,I tried to remain child-like, all I achieved was childish'' but your solution will not work. The views are nice however, I don't need views to retrieve the CURRENT situation. I need an user to enter a date and after that I have to retrieve the records from the necessary tables that are valid on the entered date.

So in your solution the date entered by the user has to be used to generate a view....?

 

[blom0344]

Hello Serge,

I think you do not understand the effect a view can have on your problem. If you create views in the database that are defined as JimboPalmer suggest they will , when called, in an SQL only deliver the (restricted) dataset you are interested in , in the first place. Thus, there is no need to do the comparing check , cause the data you can get at is already in the right range (i.e. from the correct status, namely active records). At report and universe level this can do away with a lot of additional conditions.
Whether it will solve your problem depends on the relationship between the 3 tables you mention, maybe you can give some more details about this!? T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[SergeS]

Hi all,

I understand how views work. What I don't understand is the following.

Take f.e. the Department-table. I want a view on that table which only contains the rows that are valid on the date entered by an end user.

How do I get the date entered by the end user in the where-clause of the create view statement?

In the example given by Jimbo the sysdate is used. This is a known global variable. However, by which means can I refer in my SQL to the entered date in my BO environment?

Cheers,

Serge

 

[blom0344]

Hi Serge,

You are entirely right about no being able to change interactively the SQL that creates the view.

Neither I or , I think Jimbo Palmer , is stating that you can. What is meant is , that given tables with - lets call it - active and inactive records, every generated SQL that only is aimed at active ones you have to state something in your where-clause to avoid inactive ones. With a view you prepare an active set, so the interactive SQL no longer needs the conditions in the where-clause.

You still need the correct join-strategy between tables to get the proper combination of data from your sources. But that is something you won't be able to construct by adding restrictions to joins in the universe. In my last reaction I asked about details about the 3 tables and the way they are related. That should give us a bit of a handle. But that is up to you ... T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[SergeS]

Ok, here are the details about the 3 tables.

Division (1:1) - (1:N) Department
Department (1:N) - (0:N) Employee

So, a Division has 1 or more Departments and a Department belongs to exactly one Division.

In every table history as well as actual records are stored. Every table has the following columns:
- effective date
- enddate

The effective date is mandatory. Enddate is not and values may be a dates in the past, in the future or NULL.

Imagine I want to know how many employees were working in Division A at January the 31st, 2002. In the joins to be performed only the records which were valid on January the 31st, 2002 should be involved. This holds for every table.

I hope these details give a well enough picture of the situation.

Thanks for all the explanations.

Greetings,

Serge