cancel
Showing results for 
Search instead for 
Did you mean: 

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

nscheaffer
Active Contributor
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Noel,

try to calculate that field in the Data foundation, there you have a setting "Use database-spacific SQL" :

Give it a try and let us know.

Best regards,

Victor

nscheaffer
Active Contributor
0 Kudos

That worked.  I found I can also do the same sort of thing with derived table.

Thanks!

Answers (0)