Skip to Content
0
May 15, 2013 at 06:25 PM

Is different SQL syntax in use in a single-source vs. multi-source universe?

67 Views

I am trying to build a universe based purely on a Calendar table that contains a wide range of dates and various properties of those dates. I want to create a series of additional properties within the universe such as First Date of Quarter, Last Date of Quarter, First Date of Week, etc.

Here is a screen shot of my table with a small range of dates and the First Date of Quarter.

When I specify the underlying data foundation to be Single Source and define my First Date of Quarter dimension in the business layer as follows...

CONVERT(DATE, DATEADD(Quarter, DATEDIFF(Quarter, 0, dbo.Calendar.dt), 0))

It works as expected...

However, when I choose Multisource-Enabled and specify the First Date of Quarter to be...

CONVERT(DATE, DATEADD(Quarter, DATEDIFF(Quarter, 0, @catalog('COMMON')."Common.dbo"."Calendar"."dt"), 0))

I get the following error when I valid the SQL Expression.

The details of the error are

Error:

[Data Federator Driver] [Server] Encountered "(" at line 2, column 24.

Was expecting one of:

")" ...

Cause of Error

Encountered "(" at line 2, column 24.

Was expecting one of:

")" ...

It seems that different SQL syntax is permissible depending on if you choose Single Source and Multisource-Enabled for the data foundation type as suggested by the dialog box when creating the data foundation.

I would like to create Multisource-Enabled universe, but it is not going to work for me if otherwise allowable SQL syntax is no longer valid. How do I work around this?

Thanks,

Noel

Attachments

pastedImage_0.png (43.9 kB)
pastedImage_3.png (39.1 kB)
pastedImage_9.png (71.1 kB)
pastedImage_13.png (75.8 kB)