Challenge for the PROs: two new tables will not talk properly in a "Cube" that perfectly

[Anonymous]

I give KUDOS and mark as Solved!

We have two new tables. They are like Fact tables with Many rows of the ProductKey.

I can get then individually to talk to the DIM_Products table but can not get two to work at the same time. I’ve tried every type of connection (except Many:Many).

I suspect its to where they connect.

Here is the main schema showing the two new tables. Excepting the new tables this “Cube” works perfectly.

Descriptions

Direct Query

DIM_Product is a true Dimension table: one row per Key (ProductKey)

Vw_Product_Tariff is a Fact table: Many rows of the same ProductKey

DIM_ProductPricing is like a fact table: Many rows of the same ProductKey

vw_ProductTariff talks to DIM_Product

DIM_ProductPricing

Dim_ProductPricing and vw_Product_Tariff can not be in the same visual together

Any ideas? Do we need a bridge table (why since DIM_Product is a true Dimension table)?

Schema:

[BA_Pete]

Hi @Anonymous ,

Difficult to say without seeing the data/model, but the first thing I'd do is remove one or both instances of bi-directional filtering between your true dim table and your facts.

Depending on how you are trying to visualise the fact data, there's a possible feedback loop created i.e. both fact tables can filter the dim table at the same time, and each of their filter effects on the dim table get passed over to the other fact table, which changes the filter that that fact table passes to the dim table etc.

Pete

[Anonymous]

Nope, I did 1:Many and vw_Product_BOM doesn't talk to DIM_ProductPricing. 

Individually w_Product_BOM and DIM_ProductPricing talk to DIM_Product.

I've connected many table for a SQL database and never had this issue. Lucky I guess. 

[mahoneypat · Accepted solution]

The single direction relationship is the way to go with your model.  There are other ways to pass filters around (e.g., CROSSFILTER, USERELATIONSHIP, TREATAS) without going to a bi-di relationship.  You may initially get an error in a visual with two unconnected tables, but when you add the right measure that uses both, it will work.  Please provide more detail on the analysis/visual you are trying to do/generate.

Regards,

Pat

[Anonymous]

@mahoneypat ,

I dug deeper and found out an employee of the client made the tables. She's good but she doesn't know how to set up databases. The tables need to changed. Most likely parced into more tables. The tables are more like two or fact tables crammed into each table. One has multiple currencies and no currency indicator! The company uses tables/fileds in USD only for reporting. Crazy!

[Anonymous]

@mahoneypat ,

Thank you for your response. 

If the answer is the tables are not built correctly or a Bridge table is maybe needed let me know those thoughts. Something is wrong and it could be bad design. I know the Database Designer built the tables but I believe he was not involved in the design. 

There are many reports that will need this. 

Below are outputs. The VW_Product_BOM_and_Tariff_History KEY will pull in with DIM_ProductPricing if I count the KEY. It only delivers a few rows so it is having trouble talking. Without an aggrigation function it delivers an error message. 

Again, Individually vw_Product_BOM and DIM_ProductPricing talk to DIM_Product just fine.

[Anonymous]

@BA_Pete ,

Thank you. I'm trying that again. I gave you Kudo because you delivered some explaination (some value added). Cheers!