CODEWITHSUNDEEP
sql-serversql-server-2008ssasbidsssas-2008
MattClarke
MattClarke

Reputation: 1647

Deploying cube generates ddl300 error with FormatString

I modified a fully working Analysis Services cube to change the format of several date fields. Now when I try to deploy it I get the error...

The ddl300_300:FormatString element at line 901, column 41 (namespace http://schemas.microsoft.com/analysisservices/2011/engine/300/300) cannot appear under Envelope/Body/Execute/Command/Batch/Alter/ObjectDefinition/Database/Dimensions/Dimension/Attributes/Attribute.

Versions

Details

In several dimensions I have date attributes that are used as simple display attributes, i.e. not involved in any complex date-time manipulation. Their FormatString property is empty. When I view the cube (e.g. via Excel) the dates appear with both date and time.

I want to show just date, not time. So I changed FormatString to Short Date for each of the date attributes. (Also tried d/MM/yyyy with the same result.)

As mentioned above, deploying the changes now fails.

Other discussions

This issue has been mentioned by others on MSDN and TechNet but not lead to any resolution apart from backing out the changes to FormatString. It appears that BIDS Helper generates an XML spec that is incorrect for SQL Server 2008R2.

Request

If you have seen this before and fixed it please let me know how.

Alternatively, if you are able to use the FormatString property with 2008R2, could you tell me what XML gets generated? If I knew that I could probably edit the XML to bypass the code generated by BIDS Helper.

Upvotes: 1

Views: 1049

Answers (2)

cdonner
cdonner

Reputation: 37708

I suspect myself that installing BIDS helper 2012 creates an incompatibility - SQL 2008 R2 does not understand this attribute, and the fact that there is no error during build, only during deployment to SQL 2008, supports this.

I just ran into this and had to manually remove this line from the dimension source code. Once I had selected a format, I could not get rid of it again.

      <ddl300_300:FormatString>Standard</ddl300_300:FormatString>

I have not found a workaround - it is necessary to clean up the versions for this error to go away and for the format string to work again. At least, this is what Jeremy suggested as well in this thread.

Upvotes: 1

MattClarke
MattClarke

Reputation: 1647

As you can see from comments, no-one has been able to suggest why the FormatString setting does not have the desired effect. It may be a misunderstanding of that property's designed function, or a bug, or a subtle configuration mistake.

I worked around this by implementing a more long-winded approach: added a formatted char field to accompany each date field in the dsv. Then separated KeyColumn from NameColumn in each case. Then the FormatString property becomes irrelevant.

Upvotes: 1

Related Questions