Deployment of the FoodMart 2005 SSAS project

Question

Deployment of the FoodMart 2005 SSAS project

Leendert van Staalduinen

SSC Eights!

Points: 877
More actions
March 20, 2007 at 3:03 am

#172466

I am trying to deploy the FoodMart 2005 SSAS project for the book "Microsoft SQL Server 2005 Analysis Services" (Sams, 2007), but it does not work.
Initially, deployment seems to work: a new FoodMart 2005 database is created in SSAS. I can see it in SSMS, and all database objects seem to be created in SSAS, that have equivalent names as in the FoodMart 2005 project, as shown in Business Intelligence Development Studio.
Even then, I get three error messages, and deployment failed:
·       The dimension '[PCC4200\Administrator]' was not found in the cube when the string, [PCC4200\Administrator], was parsed.
·       The END SCOPE statement does not match the opening SCOPE statement.
·       MdxScript(HR) (24, 1) The END SCOPE statement does not match the opening SCOPE statement.
Obviously, I did not modify anything in the design of the FoodMart 2005 database, so there is no dimension '[PCC4200\Administrator]' (that is: not created by me). PCC4200\Administrator is the account I use on my PC.

Further details: I followed suggestions given by Ms. Gorbach (found on the internet, dd. March 2, 2007), about TestRole members.

Could you please give me any help.

Thanks, Leendert.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Can you post the contents of the MDX script for the cube? ie open it in dev studio, go to calculations, change to script mode (from form mode) and the copy from there, paste to here.

Steve.

Leendert van Staalduinen SSC Eights! Points: 877 More actions · Answer 2

Steve, I have no idea why you suggest me to go to the Calculations tab, but there seems to be the problem. I commented out the calculations of the HR cube, and now the deployment completed successfully.

The code that does not work correctly in the HR calculations:

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].cellsecurity

AS IsAncestor(

LinkMember([Employee].[User Name].[User Name].members("["+UserName()+"]"),[Employee].[Employees])

,

[employee].[Employees].CurrentMember)

OR

LinkMember([Employee].[User Name].[User Name].members("["+UserName()+"]"),[Employee].[Employees])

IS

[employee].[Employees].CurrentMember,

VISIBLE = 0 ;

CREATE MEMBER CURRENTCUBE.[MEASURES].cellsecurityexpression

AS false,

VISIBLE = 0 ;

CREATE SET CURRENTCUBE.

AS Filter([Employee].[Employees].members,[Employee].[Employees].CurrentMember.Properties("User Name")=UserName());

scope (Descendants(LinkMember([Employee].[User Name].[User Name].members("["+UserName()+"]"),[Employee].[Employees])));

[MEASURES].cellsecurityexpression = true;

end scope;

I assume that something goes wrong with the syntax around the UserName() function. For this moment it is beyond my skills and knowledge of MDX to correct this. The above has been described by Melomed et.al. on p. 729-730 and is part of Dynamic Security.

Leendert.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Your assumption is correct. In each location that the UserName() function is being used (which returns your Username, hence the [machinename\administrator] reference in the errors), it's trying to string that together in to the specification of a dimensional member from the Employees dimension. Since the data that populates that dimension is dummy/static, it obviously doesn't contain your username.

If you wanted to, you could modify a single entry in table thats the source of the Employee dimension and change the UID to be your <machinename>\<username> and then you could uncomment the HR/security code and see how it all works.

Glad you got it sorted out

Steve.

Leendert van Staalduinen SSC Eights! Points: 877 More actions · Answer 4

Steve,

Thank you for your answer. I added a new record to the Employee table with my personal facts, including "PCC4200\Administrator" in the user_name field. I commented out the HR/security code, but it raised the very same error. As I am in my learning stages yet, I think this kind of errors overpowers my debug capabilities. I think it is better for me to enhance my skills, before I really try to find out what goes wrong.

I have sent an e-mail on this topic to the writer's / the book's Home Site: http://www.e-tservice.com/default.htm, but I did not receive any response yet.

Thank you for your kind responses, Leendert.

pmanse SSC Enthusiast Points: 115 More actions · Answer 5

You and I followed the same route trying to get to any of the authors (Edward Melomed, Irina Gorback, Alexander Berger, and Py Bateman). I never heard from them either. At this point I haven't tried this fix, but it looks good. Thanks for the answers.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

*Theoretically* you should be able to get this to work by changing one of the entries in the *source table* that feeds the Employees dimension. There must be a UserName property/field in that table that is the source column for the [Employees]. attribute in the dimension. Finding that table, making the entry/change (to be *your* username) should result in the calculated members and set statements to resolve, which I can only assume are then reference in one (or more) Roles within the cube.

HTH,

Steve.

pmanse SSC Enthusiast Points: 115 More actions · Answer 7

I got it working last night using one of the suggestions above, i.e. to add myself to the Employees table in the Food Mart database. The #1 employee is the president. ID #3 was open so I added myself there duplicating all the entries in #2 except for the first name, last name, and domain/username where I put my own credentials. Next time I processes the cube, it all worked fine.