Fact & Dimension Relationships Not Working In Cube????

[torre2011]

I am experiencing something strange with my Transformer 10.1 Cube...at least to me :)

So I created a cube using 4 reports from report studio as sources (Time, Distributor, Item, Fact), these reports all point to their respective query subjects within the Framework Package. After creating the cube, and then nesting a level from the Distributor and Item dimension along with the Time, it looks like an outer join is being applied.

So when I attempt to run a similar report against the relational model (which is the same package used for the reports in the data source of the cube), i get results that look like an inner join!! So in FM, the dimensions have joins to the fact like a classic star schema.

So, I guess what I am asking is..should I expect Transformer cube to provide the same results, maintaining the relationships as seen in the FM package???

I hope I am making sense...please ask if you think you can assist. I am really in a bind here to provide answers to my business team.

Thanks!

[Lynn]

I think you are observing normal OLAP behavior.

The cube will show every Item within each distributor (or every distributor within each item, depending on how you nest) regardless of whether there is a fact present at each intersection. Using suppression or filtering within the report gives you options to not show rows or columns for which there is no activity.

There may be cases where the absence of activity is an important part of the analysis.

[torre2011]

Yes...that is what I thought!  It has been awhile since I have built cubes, and when the business started their UAT, they submitted bugs stating that they were unable to retrieve data within Business Insight Advanced when they nested lets say a level from the Retailer Dim (which has the largest number of records) with the Distributor Dim...they were using row suppression in BIA.

I wanted to ensure that I have exhausted efforts from within the tool to assist this...but I believe that in order for them to see analaysis at the granular level between dimensions, they may need to go to aggregated tables using Query Studio.

If there are any other suggestions or if I may have missed something....please let me know.

Thanks Again!!

[Lynn]

Row suppression is notoriously slow. Using a filter function might be the better way to go.

I don't know what you mean by aggregated tables using Query Studio, but almost anything you can do in QS is going to be possible within BIA. BIA supports both relational and dimensional style reporting.

[cognostechie]

I am experiencing something strange with my Transformer 10.1 Cube...at least to me :)

So I created a cube using 4 reports from report studio as sources (Time, Distributor, Item, Fact), these reports all point to their respective query subjects within the Framework Package. After creating the cube, and then nesting a level from the Distributor and Item dimension along with the Time, it looks like an outer join is being applied.

So when I attempt to run a similar report against the relational model (which is the same package used for the reports in the data source of the cube), i get results that look like an inner join!! So in FM, the dimensions have joins to the fact like a classic star schema.

So, I guess what I am asking is..should I expect Transformer cube to provide the same results, maintaining the relationships as seen in the FM package???

I hope I am making sense...please ask if you think you can assist. I am really in a bind here to provide answers to my business team.

Thanks!

Hi -

When you use multiple data sources in Transformer, make sure that you check the scope because you are using multiple sources, not just one source. Hence the scope relationship between the sources has to be set in order for the cube to associate dimensions with the measures from multiple sources. When you check the scope, if you see red or white color, then it's a problem and that is one reason for wrong association. Wrong association will show the data that 'looks' like an outer join has been applied.

A cube does not deal with joins. There is no concept of outer or inner join in a cube and it has nothing to do with the relationships set in FM. It deals with association of items from data sources. The association has to be properly set otherwise the data can be wrongly aggregated.