Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for July 20th, 2008

SSAS Deployment Wizard Retaining Permissions

Posted by denglishbi on July 20, 2008

Had a great question last week at the MNPASS SQL Server User Group in regards to using the Analysis Services Deployment Wizard and I just wanted to post the follow-up.  I have read about the wizard, but actually have never used it (but you need to know about it for the MCTS test 70-445 or 70-448).  I have just always deployed my databases through BIDS and have always had the roles and permissions defined in the solution.  This provided me a great opportunity to explore the wizard.  What I needed to find out was if the issue of losing the database permissions had been fixed in 2008 (refer to this MS KB article for the current issue here – basically the permissions do not get pulled into the XMLA script using the ‘Retain roles and members. The roles and members will not be deployed.’ option).


So first I tested this out in 2005 to verify that this was an issue and there wasn’t a hidden checkbox somewhere to resolve the issue.  I generated the script and reviewed it and it did pull in the roles and members, but no permissions (database, dimensions, etc.).  So whatever permissions were setup on the server would be wiped out once the script was executed.  Here is a snippet of the script and if you performed a search in the file you would see that no permissions were included.


I then setup a project in BIDS 2008 (VS 2008) and performed the same steps as with 2005 and everything was pulled into the script properly (cube, database, and dimension permissions) this timeOpen-mouthed

ssas2008_dw_script1 ssas2008_dw_script2

The one thing is though if you are trying to separate the security control and permissions from the developers and DBAs this really won’t fix that problem because the wizard needs to extract the permissions and include them in the file to be executed against the SSAS database.  This means that the developers will be able to view and modify the permissions prior to executing the script.  I suppose this would mean that the DBAs would need to take over the responsibilities of using the deployment wizard or at least have a backup script of the existing Roles setup on the server prior to deployment.

Posted in Analysis Services | Tagged: | 1 Comment »

Quick Feedback on the 71-448 (70-448) Beta Exam

Posted by denglishbi on July 20, 2008

I took the beta exam 71-448 for the MCTS Microsoft SQL Server 2008 – Business Intelligence Development and Maintenance (70-448).  I would definitely check out the prep guide prior to going into the test to prepare yourself for what will be on the test and that is available on Microsoft’s site here.  In an earlier posting I posted the link stating that you can take this test for free until July 31st if you can get a spot.

I was a little surprised that there was 70 questions. I didn’t think there were that many on the SQL Server 2005 exam (70-445), but I could be mistaken.  I thought there were only around 54 or so, but it was over a year ago that I took it.  You definitely need to know SSRS inside and out.  As you can see in the prep guide at least 30% is focused just on this topic.  The rest is pretty much a split between SSIS and SSAS with a handful of Data Mining questions.

The other thing was that even though it states it is SQL Server 2008 you wouldn’t have known it.  A little surprised that none of the new features were brought up and a little disappointed.  Seems like they did a search and replace on 2005 with 2008.  If you took the 2005 exam (70-445) or have the Transcender prep tests you are all set.

If you have the time before the end of the month I would definitely recommend taking it now for freeSmile  In eight weeks I will find out if I passed or not, so until then I just have to wait patiently.

Posted in Training | 1 Comment »

Did you know? SQL Server 2008 includes Dimension Designer Enhancements

Posted by denglishbi on July 20, 2008

SQL Server 2008 includes Dimension Designer Enhancements, new attribute relationships designer, and additional Best Practice Alerts:)

The Dimension Wizard within BIDS 2008 (VS 2008) for Analysis Service Projects has been simplified and introduces a new attribute relationship designer.

The wizard has reduced the number of screens and includes an auto-detection for parent-child hierarchies.

There have also been modifications to the Best Practice Alerts and there are now 57 alerts integrated into the designer along with a new option to add comments to them and disable them if desired.

Check out a screencast about the Dimension Designer Enhancements in SQL Server 2008 here.

Read more about this in the SQL Server 2008 BOL ‘Dimension Designer (Analysis Services – Multidimensional Data) ’.

Did you know? SQL Server 2008 includes Dimension Designer Enhancements from Dan English on Vimeo.

Posted in Analysis Services | Tagged: , , , , | Leave a Comment »