Setting up the Denali Client Machine

After the connection to the Tabular database was set up, I also wanted to test Crescent with a PowerPivot model created in the old fashioned way, by uploading an Excel workbook to a SharePoint PowerPivot gallery. So I downloaded the HelloWorldPicnic demo. I uploaded the included Excel file to the PowerPivot gallery and when I tried to create a Crescent report, I got the following error.

I thought if I reprocessed the original workbook and uploaded again that the error would be resolved. However, when I tried to launch the PowerPivot window, I got yet another error.

I had downloaded the PowerPivot add-in from the PowerPivot site. However, apparently that isn’t the correct version. To work with the Denali, I needed the Microsoft SQL Server Denali PowerPivot for Excel version. Once I downloaded and installed that version, I was able to launch the PowerPivot window and refresh the data. However, re-uploading that workbook to the PowerPivot gallery did not resolve the problem.

The way I ended up resolving the issue was to create a BISM connection to the PowerPivot workbook. However, I could not create it successfully using the method described in the Create a BISM Connection to a PowerPivot Workbook article available on MSDN. I had to create it using the same method I used to create my BISM connection to the tabular project that was described in my last blog post. When a PowerPivot model is deployed to SharePoint, that model is created as a database on the {ServerName}\PowerPivot instance of Analysis Services. (Yes, a full install of Denali will contain three named instances of Analysis Services as described in this blog post by Andrew Fryer). Connecting to that PowerPivot instance and expanding the Databases node in Object Explorer, I got the name of the database corresponding to my HelloWorldPicnicDenaliCTP3 workbook. I used a server name of {ServerName}\PowerPivot and that database name to create my BISM connection. Using that approach, I was able to use the Create “Crescent” Report option from the pull down menu available from the BISM connection to successfully launch Crescent.

Don’t know what my problem was or if this issue should be logged in Connect. Would be interested to know if anyone else experiences this problem.

Installing Denali CTP 3 for Remote Access

If you’ve read the Getting up and running with SQL Server Denali for Business Intelligence blog that was posted last week and you’ve got as limited an exposure to SharePoint as I do, you’ve probably had the same question I had. Okay, I install SharePoint. I don’t run the SharePoint Products Configuration Wizard. I install SP1. Hey, when do I run the SharePoint Products Configuration Wizard? What’s not clear in that excellent blog post is that when installing the SQL Server PowerPivot for SharePoint feature on the Denali install, when the PowerPivot configuration tool is executed, much more than just PowerPivot is configured. Your entire SharePoint farm is configured when that tool is executed. The SharePoint configuration databases are created in the PowerPivot named instance of your Denali DB Engine so there’s no install of a SQL Server 2008 Express Edition database.

I also created a virtual network to give Denali a spin. That article assumes all applications and services will be installed and run on the same machine. I wanted to use a Windows 7 VM that already had Office Professional 2010 installed rather than have to install Office on my Denali box. To enable my Windows 7 VM to access the Denali services there were quite a few steps that needed to be performed afterwards that are not explained in that post. I’m hoping I’ve caught all of those issues and this post will help others create a similar environment successfully.

Enable Remote Access to the Default Instance of the Database Engine

By default, remote connections to all the Denali instances will be blocked. You can enable remote access by changing a few configuration settings. These settings include enabling TCP connections to the default instance of the database engine and opening port 1433 to allow remote access. My instructions assume that you followed the blog post mentioned above to the letter. You should have two instances of the database engine. The default instance which hosts the AdventureWorksDWDenali sample database (assuming that you’ve attached it), and a PowerPivot named instance which hosts your SharePoint configuration databases.

• To enable the TCP/IP protocol,
  • Open the SQL Server Configuration Manager.
  • Expand the SQL Server Network Configuration Node if not already expanded and select the Protocols for MSSQLServer node on the left hand side.
  • Right-mouse click on the TCP/IP member and choose the Enable option from the popup menu.
• To open port 1433, there are several references available on line that can describe it better than I can in this blog post. One is this video available on Technet.

Enable Access to Analysis Services

I should confess before going any further that I did not follow the instructions in the above blog post to the letter. I installed the Multidimensional server instance as the default and installed the new Tabular server on a named instance that I appropriately named Tabular. For some reason, named instances of Analysis Services are installed on a dynamic port by default. Since whatever port the Tabular instance is assigned will have to be opened in the Firewall, a dynamic port assignment is going to be a problem as it will be different port every time the service is restarted. To assign a fixed port, start SSMS and connect to the Tabular instance of Analysis Services. In the Object Explorer pane, right-mouse click on the node representing your instance and select the Properties option from the popup menu. In the Analysis Services Properties window, scroll down until you reach the Port window. Change the value from 0 to a fixed port, somewhere in the 49000 range is what I used and restart the service. Once you’ve changed to a fixed port, you need to open up the firewall. You’ll need to open the firewall to three ports, 2383 for the default instance, 2382 for the Browser service, and of course, the port that you’ve just assigned to the named instance of Analysis Services. For a better thorough description of the steps needed, see the Configure Windows Firewall for Analysis Services Access article on MSDN.

Setting Up to Give Project Crescent a Spin

I wanted to explore some of the new features of Denali so I worked through the Tabular Modeling Tutorial on MSDN from start to finish. The end result was that I now had a Tabular database to use as a source for a Crescent report. However, there were a few tasks that still had to be completed.

• I had to add a BISM Content Type to the Shared Documents library in my SharePoint site. http://msdn.microsoft.com/en-us/library/hh230813(v=SQL.110).aspx
• Next, I had to create a connection to my Tabular database in the Shared Documents library.  http://msdn.microsoft.com/en-us/library/gg492136(v=SQL.110).aspx

Once the connection to my Tabular database was created, to create a Crescent report, all that needs to be done is to select the Create “Crescent” Report option from the pull down menu on the BISM connection just created. Looking forward to playing with the new tool. Hopefully, I’ll blog about it in the future!