Circular reference caused by canonical date

[Anonymous]

Hi,

I' ve got fact table GLDataView (red) and Dimension table StructureView (orange) and fact table with Salaries (green).

GLData has data from GL, and I connect it using costcenter with StructureView (orange) to have all departments, cost centers etc.,

I also connect Salaries (green) to the same structure (orange) with budgetedcostcenter (BudgetedCostCenter may include one or more cost centers - it's wider in range).

The problem starts when I want to connect green (salaries) to the CannonicalCalendar because I would like to analyse GLData and Salaries within the same time periods. It causes circular references which is for me unfixable.

I think I could join GLDataView and Salaries into one table (on SQL side) it could solve my problem I expect, but I would like to try solving somehow under current data model.

This is (below) how I join dates from two tables into one canonical calendar (now partly hashed because of Circular reference).

[Anonymous · Accepted solution]

I couldn't find the solution to the problem but I finally got it.

Because I wanted to have analysis that could filter data using time periods (MasterCalendar), departments and cost centers (StrukturaView) accros all the visualisations.

I wanted to analyse it on different fact tables that had data from General Ledger (WynikiSzczegolView), including personal information about salaries (WynagrodzeniaWskaznikiView), budgeted data etc. All filtered by the mentioned periods, departments and costs centers. The scheme is shown below. The problem started when I wanted to connect WynagordzeniaWskaznikiView not only to the StrukuraView but to MasterCalendar as well to connect dates. It caused circular references. I tried to create canonical calendar but it didn't help.

I've realised that BudzetDzialId and ZadanieId multiplied by periods (years and months) and connected together (i.e. 201601_5, 201601_45) could be my keys. I've decided to resign from master calendar and in SQL I've cross joined to the view StrukturaView time periods in format yyyymm and I've added new. It has multiplied my rows in StrukturaView but gave me the common keys like RokMiesiacZadanieId etc. (RokMiesiac in Polish is YearMonth in English). Now I can analyse data just like I wanted.

I don't know how could it be solved in Qlik (the effect of common key including, time, DepartmentKey (...DzialId) and CostCenter Key (...ZadanieId) but I've managed to achieve it with SQL help.