Managing role-playing dimensions - QV 10 SR 1

[#1]

Hi,

I'm using QV 10 SR1 to create reports from a datamart in SQL Server 2005. In this datamart there are some dimension tables and some fact tables. A typical dimension is TIME with the date as PK. Moreover, a typical fact table is Invoices that has InvoiceDate and DueDate, where the date attribute play multiple role.

In QV, the relation between two table is maintaned respect to the underlying relationa db by using the same name for the primary key and for the foreign key.

F.e. in QV I write:

SQL SELECT Date FROM myDB.dbo.Time;

SQL SELECT InvoiceDate, DueDate FROM myDB.dbo.Invoices;

there aren't any relations between Time dimension and Invoices fact table!

But also I cannot write the same name "Date" for the "InvoiceDate" and "DueDate" of Invoices fact table!

How can I solve this issue, please? I need to maintain the link between Date (of Time) and InvoiceDate (of Invoices), and between Date and DueDate.

Many thanks

[brenner_martina]

Hi,

you have to create in the script a free table with calender beginning at Minimum date and ending at maximum date.

In the layout in all expressions you have to work with If-then-else statements to choose only dates between minimum and maximum date selected.

[#3]

Why do I create a calendar if I have a Time table in my datamart?

There could be other cases with a dimensione could play multiple roles for the same fact table. Do I manage these relations programmatically? Not in a direct manner ???

I cannot believe it!

[johnw · Accepted solution]

Can you give us some additional fields from these tables? As is, even if you could connect the two tables together, there's no data, and no reason to connect them, so it's hard for me to see what you're actually trying to do.

That said, there are two basic approaches for handling what I think you're after.

1) Create two separate calendars, one for InvoiceDate and one for DueDate.

InvoiceDateCalendar:
InvoiceDate, InvoiceMonth, InvoiceYear, etc.
2011/3/15, Mar 2011, 2011, etc.

DueDateCalendar:
DueDate, DueMonth, DueYear, etc.
2011/4/5, Apr 2011, 2011, etc.

Invoices:
Invoice, InvoiceDate, DueDate, Amount, etc.
INV123, 2011/3/15, 2011/4/5, 5000, etc.

2) Create one calendar linked to your table by a "Date Type" field.

Calendar:
Date, Month, Year, etc.
2011/3/15, Mar 2011, 2011, etc.
2011/4/5, Apr 2011, 2011, etc.

Dates:
Invoice, DateType, InvoiceDate
INV123, Invoice, 2011/3/15
INV123, Due, 2011/4/5

Invoices:
Invoice, Amount
INV123, 5000

Each has advantages and disadvantages, and the two approaches can also be combined, though I think that would get too confusing for most users.

[#5]

Hi John,

you are near about the table structure of Invoices and Calendar:

1) INVOICES: InvoiceNumber, InvoiceDate, DueDate, InvoiceDescription, Amount

f.e. 56, 2011/03/11, 2011/03/31, "text of description", 10.000 €

2) CALENDAR: CalendarDate, DateYear, DateMonth, DateDay

f.e. 2011/03/11, 2011, 3, 11

Create two separate calendars could be acceptable but it isn't the better solution because I could have a same dimension to link to a fact table more two times.

Managing a date type it isn't a fine solution.

Thanks

[johnw]

QlikView could be better at handling dates. It isn't. If the standard approaches used by experienced QlikView developers aren't good enough for you, feel free to come up with your own "fine solution."

[#7]

Hi John. Sorry, I think your suggests and, generally, the community helps are very useful. In datawarehousing often it meets fact tables linked more one calendars or other dimension and generally it is used one occurrence of calendar or other dimension. QV could be better also for other dimensions linked more one time to the same fact table.

However, it seems that using redundant dimensions could be a better solution than introducing an attribute dimension type (f.e. data type).

Thanks