Context question

[Former Member]

I have a simple loop to resolve. There are four tables; Customer, EgmLeads, MidasLeads and Time.

Each Customer record may join to one or more EgmLeads.

Each Customer record may join to one or more MidasLeads

Each Lead (both EgmLeads and MidasLeads) join to one member of time.

The easy solution is to alias Tiime, and have one Time for EgmLeads and one Time for MidasLeads. The problem with this approach is that when a user filters on time, he would assume both tables would be filtered.

If I don't alias I can create two contexts, each with three tables. The problem is a user might want all leads for a customer grouped by time. The contexts create two queries whereas I want a single query.

Can someone advise me the best way to approach this?

Thanks

Cliff

[Former Member · Accepted solution]

Use contexts as you have described. You may end up with two queries, but can still see one report block from the output. That is generally what folks expect to happen, the complex SQL is generated automatically behind the scenes and the report results are as expected. The exception will be if you have any dimension objects from your two "leads" tables, that will cause a synchronization result rather than a join.

[Former Member · Accepted solution]

Thanks for that Dave,

I have given the topic some thought and need to restate the problem. I understand a Universe to be a SQL generator, and so should be able to represent most SQL statements.

Consider what happens before I introduce the time table. I do an outer join from Cusotmer to each lead table. Each customer may have multiple leads in each of the EGMLeads and MidasLeads tables. If a customer has three EGMLeads and four MidasLeads then we get the Catesian product (within the Customer) giving 12 rows.

If I alias the Time table, making it act as two lookup tables then I can get the month for each lead and will still return 16 rows.

If I do not alias time and instead link both theEGMLEads and MidaLeads to the one Time table, I will now return rows within each month. If all the three EGMLeads were diviced over two months and the four MidasLeads were evenly divided over the same two months then I would get (2 x 1) + (2x2) = 6 rows restuned. In effect I have grouped my Catesian product by Customer and by Month.

If I understand correctly, I can not do this in a Universe. Loops are not permitted. I either alias Time or I use contexts which breaks the result set up. Is this correct?

If I go with the alias, is there a way to build a prompt in a universe so when the user filters by month they two Time aliases are selected from the one prompt and have the one value?

Thanks

Cliff

[Former Member · Accepted solution]

Hi Cliff,

I either alias Time or I use contexts which breaks the result set up. Is this correct?

Yes. You have to choose either aliases or context.

If I go with the alias, is there a way to build a prompt in a universe so when the user filters by month they two Time aliases are selected from the one prompt and have the one value?

Yes this is possible. You need to create two different condition objects in the universe.

Suppose you have created the aliases as TIME_EGMLEADS and TIME_MIDLEADS. Now the definition of the two conditions

will go as:

Condition 1:

@Select(TIME_EGMLEADS\Date) In @Prompt('Enter Date','D','Class\Lov',mono,free) 

Condition 2:

@Select(TIME_MIDLEADS\Date) In @Prompt('Enter Date','D','Class\Lov',mono,free) 

Now pull both the conditions in the query. As long as you keep the prompt string (here "Enter Date") same in both the conditions

it will be prompted only once to the user but will filter dates from both the tables.

Regards,

Rohit

[Former Member · Accepted solution]

If I call each of your leads tables a "fact" table and your customer and time tables "dimension" tables then what you have is something like this:

         + ------------- Fact ----------------+
Customer +                                    +---- Time
         + ------------- Fact ----------------+

Now in this diagram you have two paths to combine your dimension (customer and time) records. Each of these paths becomes a context. Once you set up contexts, it will not be possible to generate the Cartesian product scenario you outlined because the fact tables will never appear in the same query together. That's what contexts do... they don't eliminate the loop, they set up discrete paths through the loop to avoid multiplying rows as you describe.

Creating contexts leaves the loop in place but identifies paths through the loop. Aliases would physically break the loop like this:

         + ------------- Fact ---------------- Time (a)
Customer +                        
         + ------------- Fact ---------------- Time (b)

The problem with this solution is you have to create two sets of time objects. Every time a report writer wants to switch from fact table 1 to fact table 2 they have to swap out dimension objects and fact objects. With the context solution I outlined first, they only have to swap fact objects.

The normal solution in this case is to create two contexts, one for each fact. Outer joins are not generally used.

[Former Member · Accepted solution]

Thank you both. I will Alias Time and use the prompts to allow them to be selected together.

[Former Member · Accepted solution]

You're not going to like the results if you do that.

With all due respect, unless I have misunderstood part of your requirements, contexts are the proper solution to this problem. If you alias the time table and create two prompts, and use both prompts on a report, then you're going to include both fact tables in every query. That's not what you want to do.

Even with the aliases for time tables, you still need contexts as you have what BusinessObjects calls a Chasm Trap from your customer dimension.

Contexts are the proper answer here.