SQL BI

SQL 2005 Data Mining articles and books

This useful post of Karen Watterson has a link to the first chapter of the upcoming book Data Mining with SQL Server 2005 by ZhaoHui Tang and Jamie MacLennan. In the same post there are other useful links to articles and video about Data Mining on SQL 2005.

Excel 12 BI revealed

I've just attended the Excel 12 BI session held by Amir Netz and Allan Folting. This is the first time that Excel 12 BI functionality are shown to the public. My expectations seems to be satisfied, even if I really want to put my hands on a Beta, but this would still take some months.

These are my raw notes on the session: it's too early to make a deeper analysis.

• Existing Connections
  • Supported data connections library on SharePoint
  • Only one dialog box (not 4/5 like today)
• Stylesheet linked to PivotTable in a simple way
  • It uses the standard style gallery of Excel 12

 

• Dimension selection with checkbox click
• Drag&Drop between row and label in a panel outside Pivot Table (like Excel OLAP AddIn)
• There are different kinds of pivottable:
  • Report only
  • Report and Chart (not shown today)
  • Chart only (not shown today)
• Dimensons are placed in columns or rows automatically by default (ie Time dimension goes in columns while product goes in rows)
• Tooltip on each cell with information of what the column is (like coordinates and - I suppose - properties)
  • On dimension it gives all member attributes
• Drilldown on the same column  (it's indented but doesn't move all other columns of Pivot Table just to add a dimension level or any other attribute)
• Filter dimension member with a rich menu (sort, top, complex conditions and, of course, checkbox select like the old one)
• Field list has a filter with a combo box (just to go directly to a given measure group or dimension)
• Locale ID can be specified over a connection string (it defaults to OS Locale ID)
  • Reports maintains data correctly, even if there were specific items selected
• Support for conditional server-based formatting (colors, format string, this is a long long waited feature)
• The All Member seems to be replaced with "Grand Total" as description
• Support for named sets (there is a specific folder named Sets)
• Support for actions
  • Beautiful demo: a report built with reporting services can be called passing parameters  derived by the selected item in Excel (it leverages standard action of SQL2005 but it's wonderful to see it working in Excel)
• Support for Manual Update of pivot table (very easy: there is a checkbox on the member selection panel)
• Support for KPI visualization
  • They are visualized in the Field List as a specific category (similar to a Dimension)
    • KPI Hierarchies is maintained
  • Support for KPI value and graphic (Status & Trend)
• OLAP Formulas
  • A Pivot Table report can be converted to formulas, so it became a free-form report given todady by Excel OLAP Add-In
  • When you manually edit the OLAP formula (CUBEVALUE) there is a full IntelliSense support!
    • Connections, dimensions, members, measures, everything is suggested in the right place
    • The only IntelliSense feature to write custom OLAP formulas is great
• Integration with Office Services
  • The OLAP report (or the whole spreadsheet) can be published on a server and can be accessible by a browser without Excel
  • Report is generated and updated on the server

Excel 12 and SharePoint v3

I've seen Excel 12 and SharePoint v3 integrated with SQL Server 2005 Report Builder and Analysis Services here at PDC: we've seen very little, but enough to understand that this combination is what can really leverage on SQL 2005 BI capabilities. Tomorrow Amir Netz will show more, I'll try to give more feedback on this.

SSIS Migration Wizard as Shared Source: yes, please!

I'm sitting in DAT411 session room and I've just heared that Microsoft is considering to release SSIS Migration Wizard as shared source: please do it and do it now! :-)

I've a lot of DTS packages that could be really better migrated if only I could modify this wizard. Of course I can write a custom migration script, but it would lack many of the feature already built-in in the Migration Wizard. Please give us this wizard source so we will be more efficient in DTS migration!

Range operation with SSAS2005

Today, while digging into time intelligence wizard-generated scripting,  I discovered that Analysis Services 2005 has a new syntax for range operations that give us more flexiblity. You can define a range of members like "all members from X to the end" placing NULL in place of the undefined terminating member (we could imagine this like an infinite keyword).
Now you can write:
[Date].[Year].[2004] : NULL

Time intelligence use it into the Year Over Year calculated member, since this member must be calculated for all the years except the first one.
[Date].[Year].Members(1) : NULL

I think this sintax will be very useful even in several other circumstances, even if Date dimension is the most important one.