SSAS Deployment Wizard Retaining Permissions

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 time

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.

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

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 free  In eight weeks I will find out if I passed or not, so until then I just have to wait patiently.

Did you know? SQL Server 2008 includes Dimension Designer Enhancements

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.

SQL Server 2008 Beta Exams Extended, Open Invitation

Check out the details here – http://blogs.msdn.com/gerryo/archive/2008/07/01/sql-server-beta-exams-extended-open-invitation.aspx

Only good through the end of July, so try and register before it is completely booked.

SQL Server 2008 DBA 71-432 and SQL Server 2008 BI 71-448.

MN PASS SQL Server User Group – ‘Ask the Experts’ panel discussion

Tomorrow, July 15, from 2 to 4 p.m. I will be participating in a local SQL Server User Group ‘Ask the Experts’ panel discussion.  I look forward to discussion SQL Server and MS BI with everyone and hearing everyone’s questions.  It should be a very enjoyable time and I hope we get a great turnout.  Please bring your questions and I will be sure to stay around afterwards if anyone wants to talk more or if you need further explanation or information

I would love to talk about the new release of SQL Server 2008 coming out…will it be later this month, in August, or September.  It sounds like it is extremely close and I have been reading blogs that the pricing has already been released (apparently it hasn’t gone up). 

You can register for the MN SQL Server User Group event here or visit www.mnssug.org.

Magenic National Webinar Recording – ‘Make Better Decisions with your Data’

Aaron and I did our webinar last month and it went fairly well.  I don’t quite have the speaker phone deal worked out yet and talking to a monitor can be a little tough, but we did it.  I would have much rather been able to see everyone face-to-face, but it was a chance to evangelize and spread the good word of the MS BI Platform and talk about the BI Maturity Model.

You can check out the recording of this webinar here and if you want to download the presentation you can pull it down from my SkyDrive account here.

Microsoft purchasing Zoomix

I read Jamie Thomson’s blog here this afternoon and read the article on Zoomix’s site and Microsoft is adding more to its MDM arsenal.  Zoomix will add more MDM capabilities and add some great data quality portions.  Is this more of a move for the MDM portion or SQL Server Platform or both?  I hope that this gets incorporated into the BI Platform and adds some more to the already existing fuzzy logic and data profiling portions in SSIS.  Data quality is extremely important and there never seems to be enough time allocated to it, so having tools to help streamline the process is very helpful.

Magenic Technology Council (MTC) Summit 2008 Follow-up

Last month Magenic held its first ever MTC Summit in Chicago. I helped check people in and I was able to watch some of the great presentations by Jay Schmelzer, Rocky Lohtka, Whitney Weaver, Jason Bock, Kirstin Juhl, Michael Cummings, and Anthony Handley.  I wasn’t able to check them all out, but there was some excellent content provided and I want to thank everyone who attended and helped to make this a great event.

If you were not able to attend I have placed the presentations in my SkyDrive account here.  I would definitely recommend checking out Whitney’s presentation and T-SQL examples for accessing information with the SQL Server 2005 DMVs.  Whitney is one of our SQL Server gurus in Atlanta. 

The one thing that I really enjoyed was being able to meet and get together with the MTC members and talk about technology.  It was really great to finally meet some of the other Magenicons face-to-face.