6 Replies Latest reply: Mar 15, 2011 4:17 AM by pscorca69 RSS

    Managing role-playing dimensions - QV 10 SR 1


      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

        • AW:Managing role-playing dimensions - QV 10 SR 1
          Martina Brenner


          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.

            • AW:Managing role-playing dimensions - QV 10 SR 1

              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!

                • AW:Managing role-playing dimensions - QV 10 SR 1
                  John Witherspoon

                  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.

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

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

                  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.

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

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

                  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.

                    • AW:Managing role-playing dimensions - QV 10 SR 1

                      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.