P
Pawel
Hi,
A few days ago I posted a question on a case where the analysis cube does
not build anymore after typing a silly MDX query in the "Calculated Measures"
section of cube configuration. I did not receive any help that time, but
since then I made a few discoveries (and found a workaround) - maybe now
someone will be able to guess how to fix the problem.
Problem description:
As written above - I configured my cube and in the "Calculated Measures"
section I entered a MDX query for cube MSP_Project_Timesheet. The query was
invalid - it's syntax was incorrect (although I did not see that then).
I buiilt the cube. Build failed (of course).
I went cack to the cube configuration, deleted the MDX query and rebuilt the
cube.
Build failed again (with same message). Since then I cannot build the cube
in OPS.
I did some research and finally found out that OPS still keeps the faulty
definition somewhere and during sending the order to build the cubes to
Analysis Servicess loads this MDX query to the MSP_Project_Timesheet cube
definition.
I can see that in MS SQL Server Managfement Studio when I click on this cube
and script it as "ALTER". The script contains lines:
<MdxScript>
....
<Command>
<Text>CREATE MEMBER
CURRENTCUBE.[Measures].[Total actual work] AS 'Actual Work Billable + Actual
Work Non Billable';</Text>
</Command>
....
A workaround for the prioblem is to delete these liness fron the script,
execute it (what updates the definiotion of the cube) and re-process the
analysis database from SSMS. All is OK then... until the nightly OPS job that
rebuilds the cubes. Unfortunately, this process resets the cube definition to
the faulty one and the build fails again.
My final workaround is to disable cube rebuilding in OPS completely, correct
the cube definition manually again and set the analysis re-processing job in
SQLAgent instead.
This works fine for now, although of course each time I change a definition
of cubes in OPS I will have to do that again.
I tried to search the OPS databases for this field but failed for now (the
definition is somewhere there as I can see it in the binary file if I do the
OPS DB backup, but I had not yet had time to extract all the data in the DB
in text form to find out in what table they are kept. And they are not in the
obvious place (I mean
ProjectServer_Published\MSP_WEB_ADMIN_CUBE_CalculatedMembers. That table is
empty.).
Does anyone know where to look for that nasty MDX leftovers?
An other issue is that it seems to me like an obvious bug (data disappears
from OPS Cube Configuration GUI only).
Thanks in advance,
Pawel
A few days ago I posted a question on a case where the analysis cube does
not build anymore after typing a silly MDX query in the "Calculated Measures"
section of cube configuration. I did not receive any help that time, but
since then I made a few discoveries (and found a workaround) - maybe now
someone will be able to guess how to fix the problem.
Problem description:
As written above - I configured my cube and in the "Calculated Measures"
section I entered a MDX query for cube MSP_Project_Timesheet. The query was
invalid - it's syntax was incorrect (although I did not see that then).
I buiilt the cube. Build failed (of course).
I went cack to the cube configuration, deleted the MDX query and rebuilt the
cube.
Build failed again (with same message). Since then I cannot build the cube
in OPS.
I did some research and finally found out that OPS still keeps the faulty
definition somewhere and during sending the order to build the cubes to
Analysis Servicess loads this MDX query to the MSP_Project_Timesheet cube
definition.
I can see that in MS SQL Server Managfement Studio when I click on this cube
and script it as "ALTER". The script contains lines:
<MdxScript>
....
<Command>
<Text>CREATE MEMBER
CURRENTCUBE.[Measures].[Total actual work] AS 'Actual Work Billable + Actual
Work Non Billable';</Text>
</Command>
....
A workaround for the prioblem is to delete these liness fron the script,
execute it (what updates the definiotion of the cube) and re-process the
analysis database from SSMS. All is OK then... until the nightly OPS job that
rebuilds the cubes. Unfortunately, this process resets the cube definition to
the faulty one and the build fails again.
My final workaround is to disable cube rebuilding in OPS completely, correct
the cube definition manually again and set the analysis re-processing job in
SQLAgent instead.
This works fine for now, although of course each time I change a definition
of cubes in OPS I will have to do that again.
I tried to search the OPS databases for this field but failed for now (the
definition is somewhere there as I can see it in the binary file if I do the
OPS DB backup, but I had not yet had time to extract all the data in the DB
in text form to find out in what table they are kept. And they are not in the
obvious place (I mean
ProjectServer_Published\MSP_WEB_ADMIN_CUBE_CalculatedMembers. That table is
empty.).
Does anyone know where to look for that nasty MDX leftovers?
An other issue is that it seems to me like an obvious bug (data disappears
from OPS Cube Configuration GUI only).
Thanks in advance,
Pawel