IDT generating group by on some dimensions, not others

[Former Member]

Hi,

I started running into a problem yesterday with a newer universe I created.  I have a measure for count of account number with a sum projection (among other measures) and many different dimensions.

Yesterday I created a webi report with account count (the measure) and a diagnosis code dimension.  There were no problems.  Then I added in visit end fiscal year (representing the account end date fiscal year).  I got the generic "[Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared."

I went back into IDT and created a query there and I get the same results.  However, if I remove the visit end fiscal year dimension and add visit start month, the query runs fine.

I discovered that, with certain dimensions, the sql that is generating is not including the "group by" for the dimensions. 

Has anyone ever seen this before? Why does it work with some dimensions and not others?

Thank you!

[natasa_nakic · Accepted solution]

Hi Laura,

I've seen this in 4.0.3 and again in 4.1. From IDT when you look at the advanced properties of the object it is correctly checked that it will be used in GROUP BY. When you build a query with that object it's not used in group by and the query fails.  I created new objects for the same DB column that looked exactly the same, all parameters looked the same, when building the SQL one objects was OK the other one was not. I could only conclude it's a bug and I think the problem happens when doing copy/paste of the object in the design tool's business layer. It does not happen every time, I'm not sure what triggers it.

My workaround is to recreate the object. When built from scratch the object always has GROUP BY.

Hope this helps. If you can reproduce it, please log a ticket as I cannot do that (internal application, don't have an S number).

Regards,

Natasa

[Former Member · Accepted solution]

Thank you Natasa.  I tried that and it did work.  I do believe this was one of the dims that I had copied from another business layer so that must be the key.

Thank you for your response.

[natasa_nakic · Accepted solution]

Hi Laura,

I'm glad it worked for you.

If you can, please open support ticket.  I do not have an S number to open it myself but will be glad to work with support if needed. This may not be a big problem but it's clearly a bug and it just wastes everyone's time.

Regards,

Natasa

[former_member206661 · Accepted solution]

Hi Natasa,

We are facing the same problem in IDT, could you explain how you check the advanced properties of the object to see if it is correctly checked for use in the 'Group By' clause of the SQL statement. I have checked the Advanced Properties, and cannot see such an option.

Regards

HS

[Former Member]

Hi

We had almost the same problem in Webi Rich Client 4.1.6 on a unv-universe (UDT). One dimension didn’t show up in Group By. In our universe we have prefix on all objects, but the object that didn’t generate a Group By was missing the prefix in the select statement. The object was connected to the Table with prefix in Tables list. We changed the select to include prefix and the problem was solved.

It is possible to see the sql (see below) in the script editor in WEBI RICH Client.

I don’t know if it is the same in IDT, where you have datafoundation and business-layer. I don’t know if UDT failed to put on prefix on this object in connection to renaming table with prefix. The diffence on the two objects is that the “failed object” have some calculation in the select statement, and the working object don’t.

Example:

Before:

Connection Database Dm_x

Prefix Dm_x

Dimension1 Select: DM_x.dbo.table.dimension1

Dimension2 Select: (datepart(year,dbo.table.dimension_date2) * 100) + datepart(month,dbo.table.dimension_date2)

Measure Count(DM_x.dbo.table.dimension3)

SQL:

Select DM_x.dbo.table.dimension1, (datepart(year,dbo.table.dimension_date2) * 100) + datepart(month,dbo.table.dimension_date2), Count(DM_x.dbo.table.dimension3) From DM_x.dbo.table GROUP BY DM_x.dbo.table.dimension1

After:
Connection Database Dm_x
Prefix Dm_x

Dimension1 Select: DM_x.dbo.table.dimension1

Dimension2 Select: (datepart(year,DM_x.dbo.table.dimension_date2) * 100) + datepart(month, DM_x.dbo.table.dimension_date2)

Measure Count(DM_x.dbo.table.dimension3)

SQL:

Select DM_x.dbo.table.dimension1, (datepart(year,DM_x.dbo.table.dimension_date2) * 100) + datepart(month,DM_x.dbo.table.dimension_date2), Count(DM_x.dbo.table.dimension3)

From DM_x.dbo.table

GROUP BY DM_x.dbo.table.dimension1, (datepart(year,DM_x.dbo.table.dimension_date2) * 100) + datepart(month,DM_x.dbo.table.dimension_date2)

Regards Brian