Removing synthetic table

[#1]

Not sure if someone can help me by just looking at the model I created but I want to remove the synthetic table somehow.

It comes about when I bring in my master calendar. My other tables don't have a general "date" field so I used the Makedate() function and the field "CreatedDate" in each table to generate a date and call it Calendar.Date.

Any help would be great.

[Oleg_Troyansky]

It looks like all those date fields are different, they all represent different events. That means, that they need to be named differently. That will resolve the problem of the synthetic keys, but create another problem - you can't use a single Master Calendar anymore.

I don't know much about your business requirements, but generally speaking, you need to pick one date as the "main" date and link it to the Master Calendar, while other dates can be "secondary" and you can populate a subset of calendar fields (such as Year, Month) just for them as needed.

For example, think of this - when the user selects Year and Month on top of the screen, what data do you want to get sleected - Accounts created in the selected Month? Or Opportunities? Or Quotes? You can't say "all of the above", because those are all different data sets. You have to pick one or another...

hope it helps,

Oleg Troyansky

[Anonymous]

How I'd deal with it:

Do not link Account Date and Contact Date to the calendar.  It is unlikely that these Dates are really important.  If needed, deal with it in front end expressions.

Concatenate Qoute, Opportunity, and Policy into one Fact table.  It is connected only to Master Calendar and to Account (logical tables in bold):

Master Calendar - (Date) - Fact - (AccountId) - Account

                                                       (AccountId) - Contact

Regards,

Michael

[#4]

What if I concatenate all the tables to the "Account" table and then call each "createddate" as "CreateDate". Then take that new "CreatedDate" field and use the makedate() function with it and call that "Calendar.Date"?

I tried it and it seems to work. Should there be any drawbacks to this?

[#5]

and what would be the difference if I did what I said in my previous message with a left join instead of a concatenate?

[Anonymous]

It is correct technically (if the only purpose is to remive Sync) - it is wrong logically.  Read again the post from Oleg.  The question is - what you expect to see when you select August 2012?  With the new data model, you get accounts and contacts created in this month, and Opportunities/Quotes/Policies for this month as well. You cannot answer a simple question like "show me Opportunities created in August 2012 by any Account", because the Accounts will be restricted to these create in August 2012 as well.  I doubt that this is what you need.

Regards,

Michael

[#7]

I was trying to provide the user with he least clicks possible to do a data reduction but what you're saying is I can choose less clicks but get less dynamic data reduction. Or have more clicks and get more dynamic data reduction.

[#8]

Your suggestion would be like the following sample. Where Account and Contact are by themselves, and would have their own date fields.

Opportunity, Quote, and Policy are concatenated into one table and linked with the master calendar by the CreatedDate.

[Anonymous]

Yes, this is what I mean.  hope it will work for you.

Regards,

Michael

[#10]

Hi

Can you give this a try.

1. Create a Master Calendar (with all the Year, Month, Day, etc. using autogenerate).

2. Dont link this to any of the tables in the data model.

3. Create similar Year, Month and Day fields in all the tables, respectively.

4. Now in the frontend in your expressions, you can use and if condition (set analysis preferred as it is faster) to compare the dates with the ones from the Master Calendar, for the respctive reports, so you get the desired output.

Hope this helps,

Best Regards,

Robinson