If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

How do I properly join data with differing granularity?

Started by hespora, 17 Jan 2024 07:38:52 AM

Previous topic - Next topic

hespora

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
CustomerDaySales
ADay1123
ADay2456
ADay3789
BDay1234
BDay2567
BDay3890

Q2
CustomerBalance
A1234
B5678

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:

Day1Day2Day3Balance
A1234567891234
B2345678905678

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.

MFGF

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
CustomerDaySales
ADay1123
ADay2456
ADay3789
BDay1234
BDay2567
BDay3890

Q2
CustomerBalance
A1234
B5678

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:

Day1Day2Day3Balance
A1234567891234
B2345678905678

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.
Meep!

dougp

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.

cognostechie

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.