two tables in webi when context used in Universe

[Former Member]

Hi,

was wondering if someone could help me. I have two fact tables joined to a couple of dimensions in the Universe. I created some contexts to remove any loops or errors.

It seems to be working, but what I don't understand is why when creating a report in webi, it creates two separate tables. One for each of the measures in each fact table. Can it create it as one?

I uploaded an image with what I'm getting.

thanks

[amitrathi239]

do you have two webi queries in the report or all  objects coming from one webi query?

[Former Member]

Hi its creating two queries since i'm using measures and/or dimension objects from the two fact tables.

thanks

[mhmohammed]

Hi Richie,

That is the default behavior when we create a report with data from multiple fact tables. Before showing that data in one table, you'll have to consider few things. The relationship (1:N) between data coming from different queries and few other thing.

Check this out, to know how to create ONE table in the report.

Thanks,

Mahboob Mohammed

[Former Member]

So its the default behavior? So if a user would want to create an adhoc report he won't be able to? Thats allot of work on the webi side of things.I tried using  aliases but with the aliases the outer joins don't work because there are other dimensions that its looking at and if a patient doesn't exist in one fact table it does not return the record at all because of the other dimensions being inner joined. this is so frustrating. thanks

When I previously said there were two queries it wasn't that I created two separate queries it created two result queries on its own.

[mhmohammed]

Hi Richie,

Are you pulling multiple measures in a query? If yes, can you uncheck the option in the Universe, Multiple SQL Statements for each measures, save and publish the universe and run the test again?

For IDT, check the last table at the end of this blog:

Or, if you have a Fan trap, which is doing that, then check this:

Dave's Adventures in Business Intelligence: Do I Have a Fan Trap?

Thanks,

Mahboob Mohammed

[Former Member]

Hi Mahboob,

thanks for the quick response. They are actually not just measures they can be dimensions objects as well. This is what I'm trying to accomplish.

I have several initiative reports that I'm running. Diabetes and CKD for example. Each are its own table and each of these have there own reports. So lets say they run the Diabetes report. In this report they get a list of patients with BP values, Last BP date, etc. Now the user would like to check on a certain patient and bring in their last CKD date. This is found in the CKD table. They would just like to drag the CKD date and add it to the report instead of running a whole new CKD report.

Make sense? can this be done? What i've tried kind of works but not as the requirement.

thanks

Richie

[mhmohammed]

Hi Richie,

Why don't you create an additional data provider (Query 2) in that report with just Patient Name, CKD Date, apply the required filters.

In the report, Merge on Patient Name, make a variable called v_CKD Date = [Query 2].[CKD Date], make it a Detail object and select the Merged Patient Name as its associated dimension object. Finally, drag that v_CKD Date column in the Table you have, you'll be done.

Thanks,

Mahboob Mohammed

[Former Member]

I could try that, but would that have to be done every a user needs to add any field from any other facts. I'm guessing yes, correct?

[mhmohammed]

Hi Richie,

Yes, you'll have to do it for any field that is not common between the 2 queries (from the 2 data sets - Diabetes and CKD). If there is a field which is common between the 2 queries, just merge on the field and drag the merged field in the table, you don't have to create a Detail object.

Again, please take a look at this:

Make sense?

Thanks,

Mahboob Mohammed