Aggregating causes join problems

[JonathanHolliday]

Hi,

I have the need to sum a value in a query with a join.
For example:
I have
Stock table including Cost of Item
Sales table including Item and Sales Vales

I want to show: Item, Sum(Sales Value), Sum(Cost)

When I define Cost and Sales Value as measures without sum() in the Universe all is fine:

[SELECT
Sales.Item,
Sales.SalesValue,
Sock.Cost
FROM
Sales,
Stock
WHERE
(Stock.Item=Sales.Item)]

When I change one of the measures to include sum(), still OK:

[SELECT
Sales.Item,
sum(Sales.SalesValue),
Stock.Cost
FROM
Sales,
Stock
WHERE
( Stock.Item=Sales.Item )
GROUP BY
Sales.Item,
Stock.Cost]

As soon as I put the Sum() on the last measure in the Universe to reduce the fetched data the query splits into two queries.

I know this is very basic stuff but what do I do in the Universe to make this aggregation work? I've got about fifty other examples that I need to get aggregating (some of my reports are > 150MB for a single page report!)

Thanks,

Jonathan (not-so-TechnicalUser)

 

[blom0344]

First, let me state that a join between two fact-tables is inherently wrong. Facttables should be joined to dimension tables.
In your setup the additional grieve will be when you have stock but no sales for a given combination. A join between fact-tables will then prevent you from getting ALL information on stock data

Basically there are two solutions:

1. Use separate dataproviders and link over the common dimension (which there should be if you can join 2 facttables)

2. Use correct contexts, so BO will create correct multiple cubes for the measures from the facttables.

All said and done, your universe design seems to be really wrong. Some transactional databases force you to sometimes join facttables, but if this is about datawarehousing, the structure is WRONG

T. Blom
Information analyst
tbl@shimano-eu.com

 

[JonathanHolliday]

Thank you for your response.

Couple of questions if you don't mind.

I'm quite used to databases, but at the basic end of the market.

I'm running BO over a long established Informix database so I have no opportunity to influence it's design.

In BO terminology what is a fact table versus a dimension table?

Why, for example, are you calling the Sales table a fact table?

In the example given (and most I can think of) I'd only want to see records from the Items table where Sales for those items are selected. Thus the join results are as desired.

So Option 1 is no good as this is a common requirement.

This leaves us with option 2. create correct context.

Any idea what might be wrong? The join used is within a single context. In the past where there is a choice of contexts that could be used BO asks which to use. This is not happening in this case.

I am painfully aware that a) the Universe I've inherited is WRONG and b) I don't have the knowledge to sort it out right away. These will be rectified in time but I need to sort this issue out first.

Thanks again,

Jonathan.

 

[blom0344]

Without knowing your database design, this is a bit difficult to answer. I assume the informix database is there for a transactional application, and not a datawarehouse.
BO likes to work with datawarehouse schemes (Star or Snowflake structures) These structures consist of one or more facttables surrounded with dimensiontables.

A fact table stores transactional data, like all orders / sales recorded. A dimension table stores information over one dimension like customer or article.

If your STOCK table has it's primary key on the item (item is unique) then this could serve as a dimension table as well. If not unique, the join between the tables will cause a cartesion product if you access them both in a query.

The universe parameter stores two settings for accessing 2 facttables in one statement. With a context for each facttable, it will split the SQL into multiple statements, with a corrected dataset to work with.

If you have no context at all I suggest fetching the data with 2 dataproviders and linking the aggregates at report level (Linked dataprovider approach) over the common dimension (item in this case)

Us veterans will have some documentation if you need it....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[JonathanHolliday]

Hi,

Excellent answer & you've read the situation well.

It is a transactional database, point taken on DW structures though.

Items are unique so no risk of cartesians.

Joined query is fine but I'm resisting as there are a number of these situations that I need to resolve & I don't need the report writing process to be more complex than it needs to be. (If I'm the best we have just imagine the other users!)

Any idea where I can find more information on your statement:
"The universe parameter stores two settings for accessing 2 facttables in one statement. With a context for each facttable, it will split the SQL into multiple statements, with a corrected dataset to work with."

Sounds like where I need to be. I do have a good looking reference manual but I'm not really sure where to start with your statement.

Thanks again,

Jonathan.

 

[skrandel]

What blom is talking about is stored in the definition of the universe. It's in the SQL tab for the universe paramters. It has to do with Multiple SQL Statements for Each measure.

Although this isn't written anywhere. BO only considers a measure to really be a measure if it has SQL aggregation on it.

For most dimensional models, you really want separate
queries. If you really don't, just turn off the feature.

It also sounds like you need to get some education. You probably need a class or some help from an expert.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com

 

[blom0344]

I agree with Steve , some formal education would be very good.
I have the feeling though , that you are in a very similar situation as myself. Apart from datamarts I have a warehouse system (in ORACLE) I report on, which is a transactional system. So, if you need some direct input, I can probably provide you with some example stuff, not unfamiliar with your Informix setup. If so drop me a mail for the purpose.......

T. Blom
Information analyst
tbl@shimano-eu.com