Configuring SQL Server Denali Reporting Services SharePoint 2010 Integration

[tweetmeme source=”denglishbi” only_single=”false”]

Update (7/22/2011): Added a note about an IE Security setting for SharePoint menus at the end of the post.  I will add a PDF of this posting either tonight or over the weekend if anyone is interested in that.

Update (7/24/2011): Added PDF download of the posting for printing, offline, and eReader availability at end of posting in the File Download section.

With the recent release of the SQL Server Denali CTP3 there have been a few changes in the installation process.  One of those is with how the SharePoint integration configuration works.  There was new post yesterday in regards to this process Getting up and running with SQL Server Denali for Business Intelligence (Crescent and PowerPivot in CTP3).  In this post is goes through the steps assuming that you have installed SharePoint 2010 first.  What if you are like myself and installed all of the SQL Server components (SQL Server ‘Denali’ CTP3 Install Experience), including Reporting Services, before SharePoint 2010?  Well the process is a little different because since you didn’t have SharePoint installed first the integration process will not be completed by just installing SharePoint 2010 next.  There are a few steps you can take and I am going to go over the PowerShell option that I took through with the help of some documentation I was provided from Microsoft with previous CTP testing.

So here are the steps that I went through to get this working (this was done on a Windows 2008 R2 VM):

1. Installed all of the SQL Server Components (installed Multidimensional SSAS and just installed the SSRS and did not configure it) – SQL Server ‘Denali’ CTP3 Install Experience
   
2. Next I went through the installation process again and installed an additional SSAS named instance in the Tabular mode.
3. Then I installed SharePoint 2010:  pre-reqs and then SharePoint (but did not run the configuration wizard – unchecked the box and closed the process down after the install).  I had to actually run the pre-req install twice and had to do a reboot prior to the SharePoint install as I recall.
   
4. Then I installed the two SharePoint 2010 SP1 files – one for Foundation and the other for Office Server (this is going to be required to get SharePoint to work with Denali for the database engine).  I believe a reboot was required after this as well. http://support.microsoft.com/kb/2510766
5. I then did the SQL Server install again to install PowerPivot for SharePoint creating another named instance of SSAS for PowerPivot.
6. The next step can be done a few different ways and I picked the new PowerPivot for SharePoint Configuration Tool (need to ensure your pass phrase meets the security requirements) – this option is available in the Tools page of SQL Installation or in the Configurations folder in your SQL Server install folder in the Start Menu. This process will configure the PowerPivot integration, activate it, and create a PowerPivot SharePoint Site Collection.
7. I then installed the ADO.NET Data Services for .NET 3.5 SP1which is required if you want to use PowerPivot against an Atom Feed in SharePoint.  A reboot is required after this install.
8. Setup the SSRS service and application (since I installed SQL and SSRS prior to SharePoint the Integration with the add-in did not complete – because no SharePoint).
   1. Run the Microsoft SharePoint 2010 Management Shell as Administrator
   2. Type the following PowerShell command> Install-SPRSService
   3. Type the following PowerShell command> Install-SPRSServiceProxy
   4. Now to start the service type the following PowerShell Command> get-spserviceinstance -all |where {$_.TypeName -like “SQL Server Reporting*”} | Start-SPServiceInstance
      
   5. Verify that the service is started in SharePoint Central Administration –> Application Management –> Service Applications –> Manage Services on Server and now look for the SQL Server Reporting Services Service to see that it has now been setup and is running
   6. Now we are going to create a new SQL Reporting Services Application in SharePoint Central Administration –> Application Management –> Service Applications –> Manage Service Applications and create a new SQL Server Reporting Services Application
      
   7. Specify a name for the Application, create a new application pool, and specify the database server name.  Down below you will also need to select the web application to associate this application with and I used the Windows Authentication for the database authentication
      
   8. Now we should be all set and you should see the new application that you just created
      
9. Now you are off to the races and can create a Reports Library in SharePoint and add the content types including the new BISM Connection File for using to create Crescent Reports! You will need to install Silverlight to get the SharePoint Create window and this will be needed for Crescent as well since it is a Silverlight application.  If you do not see the Reports Library option you will want to make sure that the Enterprise Features are enabled not only at the Site Collection Level, but also at the Site level. I left out the Report Builder Model content type because from here on out it is all about the BI Semantic Model (BISM)
   
   
10. Now you can create a new BISM Connection File and or Report Data Source to get started with Crescent (shown below in the respective order). After you create these you will not see them in the library.  In order to see them you can go to the Library tab in the Ribbon and select the ‘All reports and dashboards’ option for the view and then you will see the connection and data source files you have created (by default it simply displays just the reports so it is not cluttered)
    
    
    
11. Now you can launch Crescent from the context menu from the connection and data source files you have created.  In order to create these connection files and data sources I installed and deployed the Denali Samples from CodePlex.  There is one issue with the Tabular project that you need to be aware of when you want to deploy this that has been documented here – Problems with the SSAS Tabular Model Projects Denali CTP3 samples (according to the site the samples have been fixed, so if you haven’t downloaded them you should be okay).
    
    

So there you go, you are off to the races.  You know have SSRS in the new SharePoint Integrated Mode setup running as a SharePoint Application (no longer running as its own Windows Service, completely setup and managed by SharePoint now) and you are creating awesome reports with Crescent.

Enjoy!

NOTE: If you are having an issue in say Step 9 and you are not getting menu options in SharePoint you need to go into your IE Security Settings and enable the Active Scripting.  Once you do that you should be able to use the menu options in SharePoint.

File Download:

PASSMN July 2011 Meeting Follow Up

[tweetmeme source=”denglishbi” only_single=”false”]

Update (7/20/2011): forgot to mention one thing about the meeting yesterday.  Everyone that attended received a giveaway.  That was definitely a first for me.  So if you didn’t attend the meeting yesterday you missed out because we even had some extra prizes that will be used at the next meeting.  Jason also stated that at the next meeting, which he will be presenting at, the PASS Summit 2010 DVDs will be given out as well.

Today was the MN SQL Server user group meeting.  The weather was a bit warm, around 98 degrees with humidity in the 60 to 70% range, plus there was a storm that passed through in the afternoon, so attendance was down a bit.  We still ended up with around 35 people or so overall, so that wasn’t too bad.  Maybe you have to factor in the cabin fever or probably just the fact that everyone wanted to stay in doors in the air conditioning (can’t blame people for that one).

This month the topic was ‘Ask the Experts Panel’, so not really a topic, but a good opportunity for the community members to bring their questions and ask for pointers and get some feedback from not only the panel members, but other attendees as well.  The other thing that we do at these panel meetings is have each person on the panel provide some tips/tricks.  As usually there were some really good items mentioned at by each panel member.

Here is a brief recap of what was mentioned (and if I am not spot on you can always check out the video once it is uploaded to Vimeo – PASSMN videos):

Lara Rubbelke (@SQLGal) – talked about a new project that she helped work on and just released this month on CodePlex that helps setup and row and cell level security on SQL Server databases

SQL Server Label Security Toolkit: The Label Security Toolkit provides tools and techniques for using Microsoft® SQL Server (versions 2005 through 2008 R2) to implement row-level security (RLS) and cell-level security (CLS) based on security labels. The major components of the Toolkit are: • The Label Policy Designer application • Documentation • Examples showing the implementation of row- and cell-level security in different scenarios

Mark Vaillancourt (@markvsql) – talked about setting up a database tables to store formatting colors and settings for SQL Server Reporting Services reports. This data can then be pulled into a the report in a dataset and leveraged into the report design using the new Lookup function in SQL Server 2008 R2 SSRS.  This way if someone decides that they would prefer report fonts, colors, etc. to be different they can be changed in a central location and will be seen when the reports are refreshed.  This is much easier instead of touching each report, especially if you have hundreds of reports.  This concept is very similar to this posting – User Preferences and Styling and there are other options as well using techniques like this.

Jason Strate (@StrateSQL) – talked about the SQL Server Study Group that he has going to help people out that are looking to go after certifications and also just to get together and talk about SQL related topics.

Dan English (@denglishbi) – I mentioned that if you are doing BI projects make sure that you have BIDSHelper installed, it is a must have and loaded with really cool items that are very handy. I also talked about enabling Precedence Constraint Labels in the Tools Options of BIDS and also setting the ShowAnnotation property to ‘ConstraintOptions’ for Completion constraints that have expressions used. I also talked about setting up a default NULL measure on Analysis Services Multidimensional cubes. This is good for performance reason as well as some MDX reasons when people use functions without referencing a measure (not knowing that they are actually referencing one). You can also see some odd behavior when running queries to retrieve lists of dimension members or placing hierarchies on rows or columns in an Excel PivotTable.

Here are a few references that might be helpful to go over a couple of items that I talked about:

• Integration Services (SSIS) Package Designing Tips
• SSAS – Speed up dimensions using a NULL default cube measure

Bill Preachuk (@billpreachuk) – talked about leveraging Virtualization for servers and some of the benefits talked about an actual case study that he observed.

Zach Mattson (@WIDBA) – talked about all of the cool things he is doing leveraging PowerShell scripts being able to automate and setup performance traces on servers, monitor what people are doing on servers, etc.  The possibilities are really endless and PowerShell is a great tool to leverage to help automate and streamline activities.

As for the rest of the night there were lots of good questions and discussions.  He got to talk a some about new SQL Server ‘Denali’ features, which by the way you can check out a recent post about my top items – Top 10 BI Reasons to Upgrade to SQL Server ‘Denali’ (SQL11), as well as do a little planning afterwards in regards to MN SQL Saturday #99 which just went live.  Registration, call for speakers, and call for sponsors is all live now – this year there is a $10 lunch fee and you will be prompted during registration to use PayPal for the $10 (not bad for an all day free SQL Server training event).

Top 10 BI Reasons to Upgrade to SQL Server ‘Denali’ (SQL11)

[tweetmeme source=”denglishbi” only_single=”false”]

Original source post – Superior Consulting Services | July 18, 2011 at 10:36 am | Categories: Technology | URL: http://wp.me/p1kpnO-16

With the recent release of the Community Technology Preview (CTP) 3 of SQL Server ‘Denali’ we wanted to point out and highlight some of the key items that are related to Business Intelligence and provide very compelling reasons for evaluating and considering the next release of the product. 

So here we go with the top 10 BI reasons to upgrade to SQL Server ‘Denali’:

10. Integrations Services (SSIS) Enhancements – lots of usability enhancements have been made such as ability to undo and redo, a new SSIS Toolbox, grouping capabilities in the data flow, simplified data viewer configurations, new data flow source and destination assistants (wizards), and improved data flow designer experience with mapping and resolving errors.  Other modifications were made so it is easier to compare and merge packages, and you have the ability to view object dependencies in SQL Server Management Studio.

9. Master Data Services (MDS) Enhancements – improvements in the user interface (UI), performance improvements with the UI, ability to incorporate data quality services into the workflow process, and a new Excel add-in client that will provide the ability to edit Master Data and publish it back to MDS and even create a model in Excel.

8. New Feature: Data Quality Services (DQS) – this is a new product offering that has been added that allows you to create a knowledge base repository of data related items that can then be used to incorporate and use for data cleansing. This will allow you to profile, improve the quality, and monitor the on-going health of your data. This new feature can be added into your Integration Services and Master Data Services processes.

7. New Feature: Database Columnstore Indexes – the VertiPaq engine first introduced with PowerPivot has now been brought to the database engine and is available to use to create indexes on columns in the relational store. This will provide a wonderful opportunity for users leveraging ROLAP technology to great improve the performance on the query processing. This index groups and stores the data for each column versus the row.  This can alleviate having to use pre-aggregated data and summary tables.

6. New Feature: Reporting Services (SSRS) Shared SharePoint Service – the SSRS integration with SharePoint will now be setup as a Shared SharePoint service just like Excel Services and PerformancePoint Services.  Everything will be managed completely through SharePoint and there will no longer be a separate SSRS Windows Service running.

5. New Feature: Reporting Services (SSRS) Data Alerts – new self-service data-driven alerting capabilities have been added to allow end-users the ability to setup and define alerts using existing Reporting Services reports. These alerts can be setup based on defined criteria and scheduled as needed to provide a mechanism to notify the user without them having to manually execute the report on their own.  This new feature is only provided in the SharePoint Integrated mode of Reporting Services.

4. PowerPivot Enhancements – with version 2 of PowerPivot there have been some major additions which include features like a new diagram view of the model, hierarchy and KPI support, ability to sort a column based on another column, multiple relationships (like role playing dimensions kind of – can access through DAX), image support, add values to rows and columns (very cool – flexibility provided in the PivotTables), reporting properties (will be leveraged by ‘Crescent’), ability to format measures, field list sorted alphabetically (instead of how they were added to the model), and most importantly…show details (drillthrough support – this will be fabulous when using with PerformancePoint).

3. DAX Enhancements – new functions have been added to help simplify and provide additional capabilities like DISTINCTCOUNT, Rank, Standard Deviation, TOPN, table functions, LOOKUPVALUE, parent-child relationship functions, SWITCH, and more.

2. New Feature: Analysis Services Tabular – the PowerPivot experience in Excel has also been added into the Business Intelligence Development Studio (BIDS) introducing a new Tabular project option.  This will allow you to take the PowerPivot model and convert it to a Tabular project and deploy the solution at the server level for a corporate/enterprise level solution that will scale and handle large volumes of data. With the Tabular models you will have the same options as in PowerPivot along with additional capabilities like partitioning, incremental updates, and role based security.

1. New Feature: Project ’Crescent’ – this is a brand new browser based Silverlight report authoring tool.  This new tool will replace the Report Builder 1.0 application that leveraged Report Models and will be able to leverage the Tabular BI Semantic Models (BISM) that you will be able to create with PowerPivot for SharePoint and Analysis Services Tabular projects. This will be a highly interactive reporting experience that will allow you to access, explore, and visualize your data and have fun doing so in the process.  This tool will enhance the existing Microsoft BI toolset and provide a very friendly self-service reporting solution for end-users to leverage.  The new feature is only provided in the SharePoint Integrated mode of Reporting Services.

If you have any questions or would like to meet with SCS to discuss how Business Intelligence can help your business please give us a call or visit or website to learn more about how we can help at www.teamscs.com.

For additional information in regards to SQL Server ‘Denali’ check out the following list of resources:

• SQL Server code-named “Denali” Resource Center
• What’s New (SQL Server “Denali”)
• Introducing Data Quality Services
• Microsoft PowerPivot Code-Named “Denali” CTP3 Release Notes
• Welcome to Tabular Projects
• Project Crescent Overview

SQL Server ‘Denali’ CTP3 Install Experience

[tweetmeme source=”denglishbi” only_single=”false”]

This week during the Microsoft Worldwide Partner (WPC) 2011 Conference, Microsoft released the CTP3 public release of the upcoming SQL Server codenamed ‘Denali’ product.  This release is for the most part feature complete and now includes some of the additional BI components that were not part of the CTP1 release that came out late last year like Data Quality Services (DQS), Analysis Services Tabular mode, Reporting Services as a SharePoint Shared Service, Project ‘Crescent’ the new Silverlight based reporting tool, SSRS Data Alerts, new PowerPivot and DAX enhancements, and much more.

Here is a walkthrough of the installation setup which contains a couple of new changes based on the CTP1, SQL Server ‘Denali’ SQL11 CTP1 Install Experience, setup (particularly for the Analysis Services and Reporting Services sections):

Express is the other option available here besides Evaluation (Enterprise)
checks to see if it is okay to install the setup files (in my case a restart was required because I had recently updated my antivirus software)
6640 MB required	Analysis Services account name information was initially blank, I added my account for this to run under and I switched the SQL Server Agent service from Manual to Automatic. Notice that a separate named account is setup for each service.
Added my user to the administrators group	I switched the two log entries from Data to Log directory to separate out the files
	New option here in the Analysis Services step for choosing what type of model you will be working with, Multidimensional or the new Tabular, so you don’t have to modify the DeploymentMode value setting in the msmdsrv.ini file (0 = Multidimensional, 1 = PowerPivot for SharePoint, 2 = Tabular). I also added my account to the Administrators group.
No changes are needed for this data file locations since they are all separated out, I wish the database engine one did this as well	I am going to eventually setup the SharePoint Integrated mode which is now a Shared Service in SharePoint, so it is completely managed and configured in SharePoint 2010 now like Excel Services, PowerPivot, PerformancePoint, etc. (no more SSRS Windows Service and not part of the Reporting Services Configuration Manager tool anymore). Even if you want to install the Native mode it appears that you do not get an option to install the SharePoint add-in component at all (bottom menu choice – no way to deselect it).
Added my account to the Administrators group	I entered my machine name for the controller name
	Here we go, let the fun begin
Everything installed successfully on the first try!	Took me around 1.5 hours for the install process going from start to finish, but that includes the whole process going through the wizard.  The actual install started at around 7:24, so around 20 minutes to install all components in a virtual environment, not bad at all.
BIDS is now in the Visual Studio 2010 IDE with Analysis Services Multidimensional and Tabular Project types. Goodbye Report Models, hello BISM!	And just like in CTP1, SSMS is also in the Visual Studio IDE
Current build number is 11.0.1440.19 for CTP3	And don’t forget, a new product offering now – Data Quality Services (DQS)

 

One thing to remember here now is that with Analysis Services we have two options, the Multidimensional and the Tabular (including the PowerPivot SharePoint option).  So if you are building a standalone sandbox test environment you can have up to three instances of Analysis Services running (one for each type of deployment mode).  This is probably not recommended (especially from the memory management side of things), but this is what you would see.

In the next few posts I will be diving into some of the new features and items in particular with project ‘Crescent’, PowerPivot v2 (lots of cool new stuff), SSRS SharePoint integration changes, and the new BISM Tabular (the new enterprise/corporate level PowerPivot version).

Here are some resource links to check out in the meantime:

• SQL Server code-named “Denali” Resource Center
• What’s New (SQL Server “Denali”)
• SQL Server Developer Tools, Codename “Juneau”…
• Project Crescent Overview
• Microsoft® SQL Server® code name ‘Denali’ CTP 3 Feature Pack
• Tutorials for SQL Server ‘Denali’
• Samples for SQL Server Denali CTP3 on CodePlex

The build number for the CTP3 release is 11.0.1440.19.

File Download:

PDF version of blog posting

Microsoft MVP Renewed for 2011 and more honors

[tweetmeme source=”denglishbi” only_single=”false”]

This month I received the email stating that my Microsoft SQL Server MVP (Most Valuable Professional) designation was renewed for my 2nd year – my MVP profile.  It is truly a great honor to be recognized for this award and to see that my contributions to the SQL Server community are not going unnoticed.  Even if I was not awarded this I would still continue doing the blogging, tweeting, forum support, presenting, helping with user groups, and help organizing events.  The reason is because my hobby is Microsoft technologies and particularly SQL Server – pretty cool that is also my job and people pay me for this.

If you are interested in the Microsoft MVP program you can find more information about this here – http://mvp.support.microsoft.com/.  There is also another program that is related called the Microsoft Community Contributor award which recognizes those that help out in the online technical forums – https://www.microsoftcommunitycontributor.com/overview.aspx.  So the contributor award is a great place to start and then expand out into the other areas of presenting, blogging, tweeting, user groups, etc. to get out and share your real world experiences and evangelize the Microsoft technologies with others.

Another recognition I received earlier this year was from Redmond Channel Partner Magazine as one of the Top 10 Microsoft Partner MVPs for 2011.  Out of the approximately 5,000 Microsoft MVPs worldwide there are around 1,000 that are associated with a Microsoft Partner.  So I was very honored and humbled once again when I found out that I had been selected and recognized by the magazine as one of the top 10 for 2011 in May of this year.  Congratulations to the others that also received this honor.

And just last week I was mentioned on the Minnesota Business Magazine’s website for ‘People on the Move’ – http://minnesotabusiness.com/people-on-the-move.  You will see an entry on July 7, 2011, recognizing me for receiving the Microsoft SQL Server MVP award for the 2nd year in a row.

This is all really cool and very unexpected.  I never thought I would ever become a Microsoft MVP.  An recognition like this was never on my radar and I think that it is just awesome.  Especially when I think how there are only around 5,000 Microsoft MVPs worldwide and only around 300 Microsoft MVPs that have the SQL Server designation.

Thanks Microsoft and congratulations to all of the other Microsoft MVPs as well.

PASSMN July 19, 2011 Meeting

[tweetmeme source=”denglishbi” only_single=”false”]

The next Minnesota SQL Server User Group (PASSMN) meeting is coming up next week on Tuesday, July 19. This month there will be an ‘Ask the Experts Panel’ where some tips/tricks will be shown along with an assembled panel of local community members that will be available to answer questions.  These are great sessions to come to and ask those questions or discussion items that you are currently dealing with or thinking about implementing.  The other great thing about them is the great discussions that are generated amongst the attendees.  So if you are planning on coming to this, please have a few questions handy that you can ask, this is how you will get the most value out of a session like this.

The sponsor for this event is Superior Consulting Services:

• Sponsor: Superior Consulting Services
• Date: July 19, 2011
• Time: 3:00 – 5:00 PM
• Location: 8300 Norman Center Drive, 9th floor, Bloomington, MN 55437
• LiveMeeting URL: https://www.livemeeting.com/cc/usergroups/join?id=3WWHDS&role=attend&pw=k_K%26%5BQ9%5DR
• Please click here for meeting details and to RSVP

Agenda:

• 3:00-3:15 : Registration / hospitality / networking
• 3:15-3:30 : Announcements etc.
• 3:30-4:45 : Ask the Experts Panel
• 4:45-5:00 : Closing comments/prize giveaways

Ask the Experts Panel:

Lara Rubbelke brings her passion for architecting, implementing and improving SQL Server solutions to the community as a Community Principal Program Manager for Microsoft. Her expertise involves both OLTP and OLAP systems, data management, performance tuning, encryption, ETL, and the Business Intelligence lifecycle. Compliance and configuration management are near and dear to her heart. She is an active board member and advocate of the Minnesota chapter of PASS (Professional Association for SQL Server) and regularly delivers technical presentations at local, regional and national conferences, user groups and webcasts. Notably, Lara has presented at Tech Ed, the PASS Summit, the Minneapolis SQL Server Launch, and delivered numerous webcasts on MSDN and TechNet. Lara authored a number of Microsoft whitepapers.

Dan English (Senior Business Intelligence Consultant, Superior Consulting Services) is a SQL Server MVP and has been developing with Microsoft technologies for over 14 years and over 7 years with Data Warehousing and Business Intelligence. He has been working with SQL Server since version 6.5 through 2008 R2 and is exploring ‘Denali’. Dan has screencasts and videos on Vimeo, is an avid blogger, and tweets. Dan was the Chair of the PASSMN user group the past two years and is now on the leadership team for the MN Microsoft BI user group.

Mark Vaillancourt is an information management with Digineer, a technology consulting firm based in Plymouth, MN. He has spent the past 4 years designing/implementing SQL Server solutions for clients in the Twin Cities area. Areas of interest include Reporting Services, Integration Services, Star Wars, Star Trek, and Improvised Comedy. He blogs at www.markvsql.com and is @markvsql on Twitter.

Bill Preachuk is a SQL Server MCTS and a local SQL Saturday presenter. He is employed by Emergent Networks in Burnsville as a DBA/Data Warehouse Designer/Data Developer. Bill has been in IT since 1991, and has actively used SQL Server and Transact-SQL since SQL Server 6.5 and Sybase version 10 (a version so terrifyingly bad that Sybase refuses to acknowledge its existence on their Wikipedia page). Bill came to the DBA camp via the way of T-SQL Development and SSIS/Informatica Data Warehouse Builder, and has a well-rounded set of SQL Server how-to knowledge.

Zach Mattson (@WIDBA) is currently the Database Administration team lead at the Patterson Companies in Mendota Heights, MN. He has over 11 years of database experience in development, architecture and administration. His main focus for the past year and a half has been developing solutions to monitor and automate the increasing amount of servers the team manages using Powershell. In addition, Zach has shared his Powershell knowledge with the IS community by publishing articles on SQLServerCentral.com and speaking at SQL Saturdays and other events on utilizing Powershell to monitor and administer SQL Server.

Jason Strate, SQL Server MVP, is a DBA with over twelve years of experience. His experience includes delivering both OLTP and OLAP solutions as well as assessment and implementation of SQL Server environments for best practices, performance, and HADR solutions. He is a SQL Server MCITP and participated in the development of Microsoft Certification exams for SQL Server 2008. Jason has presented at the SSWUG Virtual Conferences, TechFuse, SQL Saturday events, and at PASSMN user group meetings.