At the Professional Association for SQL Server (PASS) summit last November, Microsoft introduced Project Crescent, code name for a new business intelligence (BI) tool designed to make it easy for nontechnical users to create visually impressive reports. It was the BI centerpiece of another code-named technology, Denali, the upcoming release of SQL Server.
Months later, we still didn’t have many additional details -- Denali’s first community technology preview (CTP) didn’t include Crescent, CTP2 was only for Microsoft MVPs (who had to sign a nondisclosure agreement) and CTP3 was still in the works (and remains so). Luckily, at last month’s TechEd North America conference in Atlanta, Microsoft showed off Crescent in several educational sessions. From what I could see, the wait was worth it.
First, let’s discuss the design goals behind Crescent. In line with its “BI for the masses” mantra, Microsoft wanted to deliver a tool that would be easy to use and yet provide visually powerful and useful views of the data. When designing the interface, the company set up a very ambitious goal -- everything should be accessible with one or two clicks.
Once you get to use the tool, you’ll find that Microsoft pulled it off. You open a model, and Crescent reads the model’s metadata, analyzes the relationships and presents you with options for viewing the data, like tables and fields. For example, Crescent can detect parent-child relationships, and you have several choices for drilling into child records.
Let’s look at the technical details. Think of Crescent as the next generation of PowerPivot, or as Microsoft calls it, an intersection between PowerPivot and Excel. Because it is designed to run inside SharePoint, which is pricey in its Enterprise Edition and difficult to install, many people will see that as a potential obstacle. But since Microsoft is using SharePoint to glue together its BI offerings, you’ll have to accept it sooner or later -- SharePoint is here to stay.
With Crescent, you get an intuitive report designer that can build and preview reports. Once you deploy a report, users can view it in a browser, where it runs on the application framework Silverlight, Microsoft’s alternative to Flash.
As for data sources, there are several options. You can access data in a PowerPivot model that has been uploaded to SharePoint or use a BI semantic model (BISM) in Analysis Services. (BISM is the technology that makes building BI models so easy in Crescent.) These two models provide the fastest access to the data. You can also directly query SQL Server tables. But if you are going to do that, Microsoft recommends that you use column-store indexes -- the super-fleet search capability fueled by Microsoft’s VertiPaq technology -- to guarantee fast response times on the queries.
Microsoft calls Crescent “an interactive data exploration and visual presentation experience.” After watching TechEd demos by Redmond’s program managers, I have to agree. Building reports and analyzing data looks almost fun. In addition to all the usual BI tools, you get a data model that shows you measures and available fields. You can quickly change the type of graphs, and, therefore, the way you view data. You can use the same graphs you are used to in Excel -- column, bar, sparse and other. Filtering is easy to add: Traditional filters let you “Select All” or select individual criteria, but now you can type in your filters, saving you from scrolling through hundreds of items.
There are several visually impressive features. You can set up parent records to display as tiles with images. For example, you can scroll through your categories displayed as tiles. As you hover over them, Crescent displays category details in the details area, as seen in Figure 2.
Figure 2. This figure shows how Crescent displays categories as tiles. If the category has an image, it can be shown in the icon. As you select the category, Crescent automatically displays category details.
Here’s another great feature: When you view graphs, you can click on one of the bars in the chart legend and Crescent automatically highlights the details for the selected filter. Perhaps the most impressive feature, though, is the ability to use animation in a chart. Do that by changing the chart type to Scatter and then setting the value of Play Axis to, let’s say, a time measure, like a year or a quarter. As Crescent plays the animation, the points on the graph move around, allowing you to see the trend visually as it moves through all available quarters or years.
While Crescent is impressive, it is not here to replace Visual Studio’s Report Builder or Report Designer, but to complement them. Crescent lets you create interactive and visual reports with little effort; all the hard work is left to Crescent. But the price you pay is not having total control over the layout of the final report, like you get in Visual Studio and Designer. As a result, you may have to wait for a future version to provide additional ways of viewing data or for more control over the layout. You are also better off using older, proven tools if you need to do complex calculations in your reports.
Overall, Crescent’s preview shows promise, and it looks like it will deliver on Microsoft’s goal -- provide a fast and easy way to display data in an interactive manner while helping to deliver “BI to the masses.”
ABOUT THE AUTHOR
Roman Rehak is principal database architect at MyWebGrocer in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. He contributes to Visual Studio Magazine, SQL Server Magazine and other publications.