Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for July 19th, 2011

PASSMN July 2011 Meeting Follow Up

Posted by denglishbi on July 19, 2011

[tweetmeme source=”denglishbi” only_single=”false”]

Update (7/20/2011): forgot to mention one thing about the meeting yesterday.  Everyone that attended received a giveaway.  That was definitely a first for me.  So if you didn’t attend the meeting yesterday you missed out because we even had some extra prizes that will be used at the next meeting.  Jason also stated that at the next meeting, which he will be presenting at, the PASS Summit 2010 DVDs will be given out as well.

Today was the MN SQL Server user group meeting.  The weather was a bit warm, around 98 degrees with humidity in the 60 to 70% range, plus there was a storm that passed through in the afternoon, so attendance was down a bit.  We still ended up with around 35 people or so overall, so that wasn’t too bad.  Maybe you have to factor in the cabin fever or probably just the fact that everyone wanted to stay in doors in the air conditioning (can’t blame people for that one).Minnesota SQL Server Users Group

This month the topic was ‘Ask the Experts Panel’, so not really a topic, but a good opportunity for the community members to bring their questions and ask for pointers and get some feedback from not only the panel members, but other attendees as well.  The other thing that we do at these panel meetings is have each person on the panel provide some tips/tricks.  As usually there were some really good items mentioned at by each panel member.

Here is a brief recap of what was mentioned (and if I am not spot on you can always check out the video once it is uploaded to Vimeo – PASSMN videos):

Lara Rubbelke (@SQLGal) – talked about a new project that she helped work on and just released this month on CodePlex that helps setup and row and cell level security on SQL Server databases

SQL Server Label Security Toolkit: The Label Security Toolkit provides tools and techniques for using Microsoft® SQL Server (versions 2005 through 2008 R2) to implement row-level security (RLS) and cell-level security (CLS) based on security labels. The major components of the Toolkit are: • The Label Policy Designer application • Documentation • Examples showing the implementation of row- and cell-level security in different scenarios

Mark Vaillancourt (@markvsql) – talked about setting up a database tables to store formatting colors and settings for SQL Server Reporting Services reports. This data can then be pulled into a the report in a dataset and leveraged into the report design using the new Lookup function in SQL Server 2008 R2 SSRS.  This way if someone decides that they would prefer report fonts, colors, etc. to be different they can be changed in a central location and will be seen when the reports are refreshed.  This is much easier instead of touching each report, especially if you have hundreds of reports.  This concept is very similar to this posting – User Preferences and Styling and there are other options as well using techniques like this.

Jason Strate (@StrateSQL) – talked about the SQL Server Study Group that he has going to help people out that are looking to go after certifications and also just to get together and talk about SQL related topics.

Dan English (@denglishbi) – I mentioned that if you are doing BI projects make sure that you have BIDSHelper installed, it is a must have and loaded with really cool items that are very handy. I also talked about enabling Precedence Constraint Labels in the Tools Options of BIDS and also setting the ShowAnnotation property to ‘ConstraintOptions’ for Completion constraints that have expressions used. I also talked about setting up a default NULL measure on Analysis Services Multidimensional cubes. This is good for performance reason as well as some MDX reasons when people use functions without referencing a measure (not knowing that they are actually referencing one). You can also see some odd behavior when running queries to retrieve lists of dimension members or placing hierarchies on rows or columns in an Excel PivotTable.

Here are a few references that might be helpful to go over a couple of items that I talked about:

Bill Preachuk (@billpreachuk) talked about leveraging Virtualization for servers and some of the benefits talked about an actual case study that he observed.

Zach Mattson (@WIDBA) – talked about all of the cool things he is doing leveraging PowerShell scripts being able to automate and setup performance traces on servers, monitor what people are doing on servers, etc.  The possibilities are really endless and PowerShell is a great tool to leverage to help automate and streamline activities.

As for the rest of the night there were lots of good questions and discussions.  He got to talk a some about new SQL Server ‘Denali’ features, which by the way you can check out a recent post about my top items – Top 10 BI Reasons to Upgrade to SQL Server ‘Denali’ (SQL11), as well as do a little planning afterwards in regards to MN SQL Saturday #99 which just went live.  Registration, call for speakers, and call for sponsors is all live now – this year there is a $10 lunch fee and you will be prompted during registration to use PayPal for the $10 (not bad for an all day free SQL Server training event).

Posted in SQL Server, Training | Tagged: , | 1 Comment »

Top 10 BI Reasons to Upgrade to SQL Server ‘Denali’ (SQL11)

Posted by denglishbi on July 19, 2011

[tweetmeme source=”denglishbi” only_single=”false”]

Original source post Superior Consulting Services | July 18, 2011 at 10:36 am | Categories: Technology | URL: http://wp.me/p1kpnO-16

With the recent release of the Community Technology Preview (CTP) 3 of SQL Server ‘Denali’ we wanted to point out and highlight some of the key items that are related to Business Intelligence and provide very compelling reasons for evaluating and considering the next release of the product. 

sqldenali_logoSo here we go with the top 10 BI reasons to upgrade to SQL Server ‘Denali’:

10. Integrations Services (SSIS) Enhancements – lots of usability enhancements have been made such as ability to undo and redo, a new SSIS Toolbox, grouping capabilities in the data flow, simplified data viewer configurations, new data flow source and destination assistants (wizards), and improved data flow designer experience with mapping and resolving errors.  Other modifications were made so it is easier to compare and merge packages, and you have the ability to view object dependencies in SQL Server Management Studio.

9. Master Data Services (MDS) Enhancements – improvements in the user interface (UI), performance improvements with the UI, ability to incorporate data quality services into the workflow process, and a new Excel add-in client that will provide the ability to edit Master Data and publish it back to MDS and even create a model in Excel.

8. New Feature: Data Quality Services (DQS) – this is a new product offering that has been added that allows you to create a knowledge base repository of data related items that can then be used to incorporate and use for data cleansing. This will allow you to profile, improve the quality, and monitor the on-going health of your data. This new feature can be added into your Integration Services and Master Data Services processes.

7. New Feature: Database Columnstore Indexes – the VertiPaq engine first introduced with PowerPivot has now been brought to the database engine and is available to use to create indexes on columns in the relational store. This will provide a wonderful opportunity for users leveraging ROLAP technology to great improve the performance on the query processing. This index groups and stores the data for each column versus the row.  This can alleviate having to use pre-aggregated data and summary tables.

6. New Feature: Reporting Services (SSRS) Shared SharePoint Service – the SSRS integration with SharePoint will now be setup as a Shared SharePoint service just like Excel Services and PerformancePoint Services.  Everything will be managed completely through SharePoint and there will no longer be a separate SSRS Windows Service running.

5. New Feature: Reporting Services (SSRS) Data Alerts – new self-service data-driven alerting capabilities have been added to allow end-users the ability to setup and define alerts using existing Reporting Services reports. These alerts can be setup based on defined criteria and scheduled as needed to provide a mechanism to notify the user without them having to manually execute the report on their own.  This new feature is only provided in the SharePoint Integrated mode of Reporting Services.

4. PowerPivot Enhancements – with version 2 of PowerPivot there have been some major additions which include features like a new diagram view of the model, hierarchy and KPI support, ability to sort a column based on another column, multiple relationships (like role playing dimensions kind of – can access through DAX), image support, add values to rows and columns (very cool – flexibility provided in the PivotTables), reporting properties (will be leveraged by ‘Crescent’), ability to format measures, field list sorted alphabetically (instead of how they were added to the model), and most importantly…show details (drillthrough support – this will be fabulous when using with PerformancePoint).

3. DAX Enhancements – new functions have been added to help simplify and provide additional capabilities like DISTINCTCOUNT, Rank, Standard Deviation, TOPN, table functions, LOOKUPVALUE, parent-child relationship functions, SWITCH, and more.

2. New Feature: Analysis Services Tabular – the PowerPivot experience in Excel has also been added into the Business Intelligence Development Studio (BIDS) introducing a new Tabular project option.  This will allow you to take the PowerPivot model and convert it to a Tabular project and deploy the solution at the server level for a corporate/enterprise level solution that will scale and handle large volumes of data. With the Tabular models you will have the same options as in PowerPivot along with additional capabilities like partitioning, incremental updates, and role based security.

1. New Feature: Project ’Crescent’ – this is a brand new browser based Silverlight report authoring tool.  This new tool will replace the Report Builder 1.0 application that leveraged Report Models and will be able to leverage the Tabular BI Semantic Models (BISM) that you will be able to create with PowerPivot for SharePoint and Analysis Services Tabular projects. This will be a highly interactive reporting experience that will allow you to access, explore, and visualize your data and have fun doing so in the process.  This tool will enhance the existing Microsoft BI toolset and provide a very friendly self-service reporting solution for end-users to leverage.  The new feature is only provided in the SharePoint Integrated mode of Reporting Services.

If you have any questions or would like to meet with SCS to discuss how Business Intelligence can help your business please give us a call or visit or website to learn more about how we can help at www.teamscs.com.

For additional information in regards to SQL Server ‘Denali’ check out the following list of resources:

Posted in SQL Server | Tagged: , | 7 Comments »