Complete SQL 2012 Demo Build Script

There is no need to download the 28GB BI Image X (which at the time of this writing hasn’t been updated to SQL 2012 RTM).  There are instructions on how to build an all-up SQL 2012 and SharePoint 2010 demo VM with instructions for configuring all of the latest SQL 2012 tools including DQS, MDM, PowerPivot for SharePoint and more.  The full instructions are here.  This is a great way to familiarize yourself with a full deployment of the latest BI software from Microsoft.

Here’s the summary from the link above:

This how-to article will guide you through the process of building a Hyper-V virtual machine with Microsoft SQL Server 2012, the latest Visual Studio 2010 development tools, SQL Server Data Tools (SSDT), and an assortment of other software products to create a test environment ready for testing and product demos.

Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables

My buddy Wayne just brought to my attention a white paper that came out just a year ago from Microsoft that describes how to design SSAS 2005 cubes for use with Excel 2007.  I'm posting it here for my own future reference.  Here is the description:

Overview

Microsoft Office Excel 2007 takes advantage of most of the features in Microsoft SQL Server 2005 Analysis Services. To take full advantage of these features, it is important to keep in mind the end-user experience in Office Excel 2007 when you are designing cubes.
This document outlines how you can create a good end-user experience by optimizing the cube design for Office Excel 2007 PivotTable dynamic views.

Team Foundation Server 2008 RTM and 64-bit

Bad news - Microsoft is pushing 64-bit (as they should), but one of their newest enterprise products does not yet support it.  Team Foundation Server 2008 only supports 32-bit.  They have said that 64-bit support will be in the next version of TFS, "Rosario".

At least they support 64-bit SQL and SSAS, but definitely not MOSS 64-bit.  We have deployed 64-bit MOSS 2007 and it turns out that it doesn't even support 64-bit SharePoint when SharePoint is running on a different server from TFS, it doesn't even recognize that MOSS is installed:

SQL Server Default Ports

It seems like it is always hard to track down the ports that SQL Server uses, so thought that I would post them here for when I need them.  These are the SQL Server default ports.

SQL Component	TCP Port
SQL Service	1433
SQL Browser Service	1434
SQL Monitoring	1444
SQL Server Analysis Services Redirector	2382
SQL Server Analysis Service	2383
SQL Server Reporting Services	80

Excel 2003 Add-in for Analysis Services

If your company (like most companies) is still using Excel 2003 and you are implementing a data warehouse which contains SSAS cubes, you might be interested in the Microsoft Excel 2003 Add-in for SQL Server Analysis Services.  I have not used this tool extensively, but it does bring Excel 2003 closer to Excel 2007 in terms of OLAP capabilities.  Here is the blurb from the download page, I will post more as I spend time with the tool.  This is not a new find, nor is it likely news to anybody, but worth noting so I am putting it here.

The Excel Add-in for Analysis Services enables users to access and analyze data from multiple Analysis Services Cubes, and to create rich, customized reports directly in Microsoft Office Excel 2003 or Microsoft Excel 2002. This download can improve data analysis, shorten reporting cycles, and enhance your company's ability to respond to customers.
This download not only reduces the time and resources required to train users, but it also eliminates the need for organization’s to support specialized reporting systems and tools. The key business benefits of Excel Add-in for Analysis Services include:

• Visibility in Excel into business trends
• Increased speed and quality of decision-making
• Streamlined data analysis that shortens reporting cycles and saves resources
• Increased reporting flexibility through rich, highly customized, and refreshable reports
• Access to relevant information from multiple data sources

About the Excel Add-in for Analysis Services
With the Excel Add-in for Analysis Services, individual users can manage the reporting cycle from beginning to end and eliminate the need to cut and paste data from multiple systems.

• Access: Easily create and maintain live data connections to multiple Analysis Services cubes, ensuring data consistency and integrity, and combine data from multiple sources into a single report.
• Analyze: Conduct detailed analysis using native Excel capabilities. Extend the richness of analysis through “what if” and drill through capabilities.
• Author: Easily personalize and refresh report layouts. Minimize end user training and reduce reliance on IT due to Excel’s popularity and ease of use.

Slick New MDX Editor/Debugger

This is a very nice looking tool for anyone that works with MDX.  It is currently in "CTP 1, version 0.1 Alpha" release, which is another way to say "Very early in development cycle".  It works with SSAS in all of its various versions from 7.0 to 2008.  Details and download here.

Documenting a SQL Server 2005 Solution end to end

I had heard of this tool before, and it looks interesting if you ever need to document a full SQL Server 2005 solution.  It apparently documents SSIS, SQL and SSAS as well as SSRS.  The output shows data lineage, as well as the structure of your database.  Worth a look, pretty affordable too.  Too bad they couldn't make a version that worked as an add-on to Data Dude.  Here is the link to their homepage.

Load Testing SQL Server Analysis Services 2005

Have you ever had a need to load test SSAS and then report on the results?  Your life just got easier.  There is now a project on CodePlex for that very purpose.  Apparently MCS and other large internal projects have used it for a while, but it is now publicly available as a community project.  Download it here.

Interesting AJAX enabled SSAS client

I was checking my webmail today, and one of the ad-links caught my eye.  It was for an AJAX based SSAS client.  I clicked the link and it took me to IntraSight, by a company called Active Interface.  Screenshots are available at that link.  There are a lot of interesting SSAS web based interfaces floating around out there if you look hard enough.  Add this one to the list.  SharePoint integration is listed as one of the bullet points.  Pricing is pretty affordable: $1995/server.  It might be worth a look if you need a web-based OLAP tool.

Here is their feature list:

• IE browser-based SSAS client
• High-fidelity AJAX user interaction
• Interactive table and chart views
• Annotated public and private bookmarks
• View and edit generated MDX
• Sharepoint integration
• Excel, Word, and PowerPoint generation
• Publish static HTML site from bookmarks
• Web tier caching
• Search dimensions
• Filter member sets
• Sort and filter measures
• Specialized business-centric and industry-specific views