Hi there,
situation: I have two queries, coming from different areas in my data source, showing data at differing granularity. Let's say, Q1 is Sales by Customer and Day, and Q2 is Open Order Balance by Customer, but not by day:
Q1
| Customer | Day | Sales |
| A | Day1 | 123 |
| A | Day2 | 456 |
| A | Day3 | 789 |
| B | Day1 | 234 |
| B | Day2 | 567 |
| B | Day3 | 890 |
Q2
| Customer | Balance |
| A | 1234 |
| B | 5678 |
These queries work fine, and data containers show the correct data when pulling data from each of them individually. The issue is: I want to join the data and display in the same crosstab like this:
| Day1 | Day2 | Day3 | Balance |
| A | 123 | 456 | 789 | 1234 |
| B | 234 | 567 | 890 | 5678 |
How do I actually do that? When I just join the two queries on customer, the value for balance obviously inflates, as the value is multiple counted, once for each row in Q1, which rises with number of sales days. So, I can put the aggregate functions of [Balance] in the joined Query to Average/Automatic. Is it really that simple? Something about using average bothers me, it *feels* wrong, but I can't put in words what exactly is bothering me about it.
Quote from: hespora on 17 Jan 2024 07:38:52 AMHi there,
situation: I have two queries, coming from different areas in my data source, showing data at differing granularity. Let's say, Q1 is Sales by Customer and Day, and Q2 is Open Order Balance by Customer, but not by day:
Q1
| Customer | Day | Sales |
| A | Day1 | 123 |
| A | Day2 | 456 |
| A | Day3 | 789 |
| B | Day1 | 234 |
| B | Day2 | 567 |
| B | Day3 | 890 |
Q2
| Customer | Balance |
| A | 1234 |
| B | 5678 |
These queries work fine, and data containers show the correct data when pulling data from each of them individually. The issue is: I want to join the data and display in the same crosstab like this:
| Day1 | Day2 | Day3 | Balance |
| A | 123 | 456 | 789 | 1234 |
| B | 234 | 567 | 890 | 5678 |
How do I actually do that? When I just join the two queries on customer, the value for balance obviously inflates, as the value is multiple counted, once for each row in Q1, which rises with number of sales days. So, I can put the aggregate functions of [Balance] in the joined Query to Average/Automatic. Is it really that simple? Something about using average bothers me, it *feels* wrong, but I can't put in words what exactly is bothering me about it.
This is usually a challenge that would be catered for in the model, rather than in a specific report. For example, you would model Sales and Order Balance as separate star schemas, with Customer as a conformed (shared) dimension, and Date as a non-conformed dimension. That would then mean you could report from both Sales and Order Balance and get accurate, consistent results without any double-counting.
The situation you have here is described in the documentation here:
https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=udq-multiple-fact-multiple-grain-query-non-conformed-dimensions
If this really is a one-off, you could try to emulate the modelling in your report. You would basically need four queries - one for Sales Fact, containing the Sales measure(s) and key values for Customer and Date, one for Order Balance Fact containing the Order Balance measure(s) and key value for Customer, one for Customer Dimension containing the Customer key and name, and one for Date containing the Date key and any other date attributes you want to use. You would add joins between the queries with the Dimension queries defined as 1..1 and the Fact queries defined as 1..n
I don't ever remember trying to do this in an individual report, so apologies if it doesn't work.
Cheers!
MF.
QuoteIf this really is a one-off, you could try to emulate the modelling in your report.
I think Q1 and Q2 will work.
Create Q3 and join Q1 and Q2 on Customer.
In Q3, treat Balance as a dimension. That is, change the detail and summary aggregation properties to None.
As for presenting numeric dimensions the way you want in a crosstab, that may take some fiddling.
As MFGF said, it is the best practice to have a Star Schema designed DW but if you don't have it, then you can set the Usage property of the Balance to 'Attribute' in your Framework Model. This will tell Cognos not to roll up the value of the Balance field. This is usually used for fields like Price of an Item which is not an additive value.
If you do not have access to Framework Manager then you can write a SQL like this and put this in one query in Report.
Select
CS.Customer_ID,
CS.Day,
CS.Sales_Amt,
CB.Balance_Amt
from
(
Select
Customer_ID,
Day,
Sales_Amt
from <DB_Name>.Customer_Sales
) CS
left outer join
(
Select
Customer_ID,
Balance_Amt
from <DB_Name>.Customer_Balance
) CB
on CS.Customer_ID = CB.Customer_ID
After this, click on the data item in the query and set the aggregation property of Balance to 'None'. This will prevent the roll up of the data for Balance.