adamzapple.com Blog

Testing Reporting Services

1. From BI Site, click Dashboards
2. Click the Documents menu, then New Document|Report Builder Report
3. Run Report Builder 3.0 and click Table or Matrix Wizard
4. 4. Click Create a dataset and click Next
5. In Data Source Connections: click New
6. Select a connection type of Microsoft SQL Server Analysis Services
7. 7. Click Build, type in a Server name of sharepoint2010, select the Adventure Works DW 2008R2 database and click OK and OK again. Then click Next
8. Expand Measures, Reseller Sales and drag Reseller Sales Amount to the query pane
9. Expand the Date dimension, Calendar and drag the Date.Calendar hierarchy to the query pane to the left of the sales amount
10. Expand the Product dimension and drag the Product Categories to the query pane to the left of the Calendar year then click Next
11. In Arrange fields, double-click Reseller_Sales_Amount, Calendar_Year, Calendar_Semester, Calendar_Quarter and Month
12. Drag Category to the Column groups
13. Drag Subcategory below it
14. Drag Product below Subcategory and click Next and Next again and finally Finish
15. Click Run, then click Design
16. Highlight all the fields starting with Sum
17. Click the Dollar tool button
18. Widen the Categories column and click Run again
19. Click the Save button, call the report Reseller Sales By Product and Time and save it in the Dashboards library. Close Report Builder
20. Click the Dashboards library and click Reseller Sales By Product and Time
21. Drill down on Calendar Year and Product Category

Testing Excel Services

1. Click Start|All Programs|Microsoft Office|Microsoft Excel
2. Click Data|From Other Sources|From Analysis Services
3. Enter a server name of SharePoint2010 and click Next
4. Select the Adventure Works cube and click Next
5. Change the File Name to dsAdventureWorks.odc and the Friendly Name to dsAdventureWorks
6. Check Always attempt to use this file to refresh data and click Finish, then OK
7. From the Internet Sales measure, check Internet Sales Amount
8. Expand KPIs, Financial Perspective, Grow Revenue, Internet Revenue
9. Check Goal, Status and Trend
10. Scroll down to the Date dimension
11. Expand Fiscal and click Date.Fiscal. Drag it into the Row Labels area
12. Rename Row Labels to Year
13. Click Data menu, Connections, Properties. Check Refresh data when opening the file
14. Select the Definition tab
15. Delete the section of the connection string that reads: Integrated Security=SSPI; and check the Save password box. Click Yes to the warning prompt
16. Click Export Connection File
17. Navigate to the Data Connections library and click Save, OK and Close
18. Back in Excel, click File|Save & Send and click Save to SharePoint
19. Click Browse for a location and browse to the Dashboards library
20. Type a file name of Internet Growth Revenue KPI and click Save and OK to save it as a Report
21. Click OK to the warning that the spreadsheet cannot be displayed in a browser and close Excel
22. Navigate to Dashboards and click on Internet Growth Revenue KPI
23. Drill down on the date hierarchy

Testing PerformancePoint Services

1. In the BI Site, hover your cursor over Create Dashboards in the Business Intelligence Center, then click Start using PerformancePoint Services
2. Click Run Dashboard Designer
3. Click Create|Data Source and click OK
4. Name the Data Source dsPPSAdventureWorks
5. For Server: enter sharepoint2010
6. Select Adventure Works DW 2008R2 in the Database: dropdown
7. Select Adventure Works in the Cube: dropdown
8. Click the Time tab
9. For Time Dimension: select Data.Date.Calendar
10. For the Member: click Browser and expand the date hierarchy, click January 1, 2008 and click OK
11. For Hierarchy level: select Day
12. For Reference Date: enter 1/1/2010
13. For Time Aggregation select Year, Semester, Quarter, Month and Day to correspond with each Member Level then click the Save icon
14. Click PerformancePoint Content, then Scorecard, then OK
15. From the SharePoint Site tab, cllick dsPPSAdventureWorks data source and click Next
16. Select Import SQL Server Analysis Services KPIs and click Next
17. Select all KPIs to Import and click Next, Next, Next and Finish
18. Name the new scorecard All KPIs and click the Save icon
19. Click Analytic Chart, click dsPPSAdventureWorks data source and click Finish
20. From the Details pane, drag Internet Sales Amount measure to Series pane
21. Expand Dimensions|Date|Fiscal and drag the Fiscal hierarchy to the Bottom Axis pane
22. Click the Date Fiscal dropdown, deselect Default Member, expand All Periods and select FY2006, FY2007 and FY2008
23. Name the report Internet Sales 2006-8 and save it.
24. Click Create|Dashboard and click OK
25. From the Details pane, expand Scorecards|PerformancePoint Content and drag All KPIs to the left column
26. Expand Reports|PerformancePoint Content and drag Internet Sales 2006-8 to the right column
27. Name the dashboard Performance Dashboard and save it
28. Right-click Performance Dashboard and select Deploy to SharePoint. Click OK
29. In SharePoint, right-click one of the bars in the chart select Drilldown to|Customer|Country

Install and configure SQL Server 2008

1. Install SQL Server 2008 R2 Developer Edition DVD
2. On Setup Role select SQL Server Feature Installation
3. On Feature Selection select Database Engine Services, Full-Text Search, Analysis Services, Reporting Services, Business Intelligence Development Studio, Integration Services, SQL Server Books Online and Management Tools – Complete
4. Click Use the same account for all SQL Server services and enter username: domain\administrator and password: Adminpw123
5. On Database Engine Configuration click Add Current User.
6. Do the same for Analysis Services Configuration
7. On Reporting Services Configuration, select Install the SharePoint integrated mode default configuration

Install Microsoft Office Pro 2010

1. Install Microsoft Office Pro 2010 DVD and select Custom Install.
2. Select Microsoft Access, Microsoft Excel, Microsoft InfoPath, Microsoft PowerPoint and Office Shared Features and select Run All from my computer for each one

Install and configure SharePoint Server 2010

1. Install SharePoint Server 2010 DVD
2. Ensure you’re connected to the Internet
3. Click Install software prerequisites
4. Enter Enterprise CAL ProductID
5. In SharePoint Products Configuration Wizard, select Create a new server farm
6. For the Database server enter sharepoint2010
7. Enter username: domain\administrator and password: Adminpw123
8. For the passphrase enter Adminpw123 and confirm
9. Specify a port number of 10000 for Central Administration Web Application
10. Install IE8 with the minimum requirements
11. Click No to not sign up for the Customer Experience Improvement Program
12. Click Start the Wizard to configure the SharePoint farm
13. Select Existing managed account
14. Uncheck Application Registry Service, Business Data Connectivity Service, Managed Metadata Service, Search Service Application, State Service, Usage and Health data collection, user Profile Service Application, Visio Graphics Service, Web Analytics Service Application and Word Automation Services
15. Call the new top-level site BI Site
16. Select Enterprise tab on the template select and select Business Intelligence Center
17. In Central Administration click Application Management|Manage service applications
18. Click Excel Services Application
19. Click Trusted File Locations
20. Click the http:// link and uncheck Warn on Refresh
21. Click the Navigate up icon and click on Trusted Data Connection Libraries
22. In a new tab go to the home site http://sharepoint2010
23. Go to Tools|Internet Options and click Use current to make the home site the default home page and click OK
24. Remove Suggested Sites and Web Site Gallery from the tool bar
25. Type http://sharepoint2010:10000 and add it to the Favorites Bar
26. Return to BI Site
27. Click Data Connections
28. Highlight and copy the URL up to PerformancePoint
29. Return to Central Administration and click Add Trusted Data Connection Library
30. Paste the URL into the address and click OK.
31. Back in Service Applications, click Secure Store Service
32. Click Generate New Key
33. For the pass phrase enter Adminpw123 and confirm
34. Click Refresh Key and enter the pass phrase again
35. Click the Navigate Up button to return to Service Applications
36. Click Performance Point Service Application
37. Click PerformancePoint Service Application Settings
38. For the Unattended Service Account enter username: domain\administrator and password: Adminpw123 and click OK
39. From BI Site, click Site Actions|Site Settings|Site Actions|Manage site features
40. Activate SharePoint Server Enterprise Site feature

Configure Reporting Services

1. Click Start|All Programs|Microsoft SQL Server 2008 R2|Configuration Tools|Reporting Services Configuration Manager
2. Connect and select Web Service URL
3. Change the TCP Port to 8080 and click Apply
4. Click the Report Server Web Service URL. The site should open in a new browser window
5. Exit Reporting Services Configuration Manager
6. In Central Administration click General Application Settings
7. Click Reporting Services Integration
8. Highlight and copy the ReportServer URL and paste it in the Report Server Web Service URL field
9. Select Windows Authentication for the Authentication Mode
10. For Credentials enter username: domain\administrator and password: Adminpw123 and click OK then Close
11. On the BI Site, click Dashboards|Library|Library Settings
12. Under Content Types, click Add from existing site content types
13. Add Report and Report Builder Report to the Content types and click OK
14. Click Change new button order and default content type and change Report to 1
15. 15. Click Data Connections|Library|Library Settings
16. Under Content Types, click Add from existing site content types
17. Add Report Data Source to the Content types

Install PowerPivot for Excel

1. Download and install PowerPivot_for_Excel_x86 addin
2. Run Excel and install the Add-In

Set up Network Locations

1. Click Start|Computer
2. Right-click computer and select Add a network location
3. Type http://sharepoint2010, click Next, name it BI Site, next then Finish
4. In the BI Site, click Data Connections and copy the URL through the word PerformancePoint to the clipboard
5. Create another network location, paste the contents and call it Data Connections
6. In the BI Site, click Dashboards and copy the URL through the word Dashboards to the clipboard
7. Create another network location, paste the contents and call it Dashboards

Install Microsoft Sample Databases and Projects

1. Download and install AdventureWorks2008R2_RTM
2. Select AdventureWorks Data Warehouse 2008R2 and AdventureWorks OLTP and click Install
3. Select Start|All Programs|Microsoft SQL Server 2008 R2|SQL Server Business Intelligence Development Studio
4. Select File|Open|Project/Solution
5. Browse to C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008R2 Analysis Services Project\Enterprise and double-click Adventure Works
6. In BIDS, right-click Adventure Works DW 2008 project and select Deploy

You know, with all the zillions of articles on SharePoint on the Internet, I’ve never found a single one that laid out step-by-step how to install a SharePoint BI Development Environment. So I decided to do it myself. Using VMWare Workstation, a laptop with 8 Gb of RAM and all the latest and greatest Microsoft software, I created a development environment for building dashboards, scorecards, Power-Pivot, Excel Services, Reporting Services and the like.

In this first part, I’ll show you how to install and configure Windows for BI development. Keep in mind, I don’t care about security, best practices or governance for this exercise. The goal is to set up a quick-and-dirty environment just for your own use. I’ve also ignored all default settings instructions such as “click OK.” I think you’ll be able to figure that out on your own. Here goes:

1. Install Windows 2008 R2 Enterprise Edition DVD
2. Provide computer name: SharePoint2010 and restart computer
3. In Initial Configuration Tasks, click Add Roles and add Active Directory Domain Services
4. Return to Add Roles and add Application Server role
5. Go to Administrative Tools|Computer Management|Local Users and Groups and change the Administrator password: Adminpw123
6. Log off and log on as administrator
7. Go to Administrative Active Directory Users and Computers, expand domain.com, click Users and delete the user User
8. Start/Run dcpromo and Create a new domain in a new forest: domain.com
9. Provide the domain admin password: Adminpw123 and restart computer
10. Click Switch User, click Other User and enter username: domain\administrator and password: Adminpw123
11. Click Start|Run gpedit.msc, expand Administrative Templates
12. Click System, right-click Display Shutdown Event Tracker, Edit and select Disabled
13. Run regedit, locate HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon
14. Double-click the DefaultUserName and change it from user to administrator and click OK
15. Double-click the DefaultDomainName and set it to domain
16. From the Edit menu click New|String Value and name it DefaultPassword
17. Type Adminpw123 and click OK
18. Ensure that AutoAdminLogon is set to 1
19. In Initial Configuration Tasks, click Add Features and check Desktop Experience.
20. Check Do not show this window at logon and again in Server Manager and reboot.
21. In Server Manager, click Configure IE ESC and turn off IE ESC for Administrators and Users
22. Click Go to Windows Firewall, right-click Windows Firewall with Advanced Security in the left pane, select Properties and turn off the Firewall state for the Domain Profile, Private Profile and Public Profile. Close Server Manager.