MN Microsoft BI User Group 2011 Q3 Follow up

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

UPDATE (9/23/2011): A link to the Project “Crescent” Demo video has now been added and featured on the “Crescent” wiki page in the Videos section – Project Crescent Overview.

Last week I presented on What’s New with BI in SQL Server Denali (SQL11) at the Minnesota BI User Group Q3 meeting with Jin Cho from Microsoft.  Granted that in only an hour and a half we couldn’t do the topic complete justice because there is just so much to cover and it helps to provide some demos to showcase the new features and enhancements that have been made.  The topics that we covered where going over Analysis Services and PowerPivot, talked about the BI Semantic Model, Columnstore Indexes, Integration Services, Data Quality Services, Reporting Services SharePoint Integration, and “Crescent”.

I wish I would have had more time to be able to demo some of the new SSIS items, but I allocated my demo slot to show the new self-service reporting authoring tool Crescent.  Each of the topics that we did talk about can easily be their own topic for presentation, so stay tuned for the upcoming MN SQL Saturday event and the next MN Microsoft BI User Group Q4 meeting in December.  There will also be a lot of content in regards to this topic at the upcoming PASS 2011 Summit next month that I would highly recommend.

You can download the presentation from my SkyDrive account or you can check out the slides available via SlideShare as well:

I have also setup a video that goes over the “Crescent” demo that I went over during the presentation.  I believe that when I switched laptops during the presentation that I did not share the desktop with Live Meeting, so I apologize for that and since this will not be part of the recording I put together a quick video.

Project “Crescent” Demo from Dan English on Vimeo.

A link to the Project “Crescent” Demo video has now been added and featured on the “Crescent” wiki page in the Videos section – Project Crescent Overview

For more BI highlights in the upcoming SQL Server release check out this list – Top 10 BI Reasons to Upgrade to SQL Server ‘Denali’ (SQL11).

Video: PowerPivot Denali CTP3 New Features

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

I put together a video demonstration as a follow up to last week’s Minnesota Microsoft BI User Group Q3 meeting.  In this video I highlight some of the key enhancements and additions that have been made to PowerPivot in the CTP3 release for SQL Server “Denali” (SQL11).  This add-in for Excel 2010 is available to download and test out from the Microsoft Downloads area here.

The main items covered in this video demo are the following (and there might be a couple of others that I touch on as well, so listen closely and follow along):

• Measure Grid
  • Create measures inside model
  • Format measures
  • New DAX Functions
• Sort by Column
• Diagram View
• Hierarchies
• KPIs
• Drillthrough

PowerPivot Denali CTP3 Demo from Dan English on Vimeo.

 

Hope you enjoy the video and stay tuned for more!

Video: Leveraging PowerPivot Demonstration

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

I finally got around to recording the presentation.  I only did the demo to create the screenshot application I included in the presentation, I didn’t include the portions where I demo exploring the xlsx file by renaming it to zip file extension or browse the VertiPaq folders that get created in your %temp% directory in Windows (watch out for these because they can take up a lot of space over time – I currently have 16 VertiPaq folders consuming 1.31GB of space).

For the follow up on this presentation you can check out my previous posting – MN Microsoft BI User Group 2011 Q1 Follow up.  You can download the PDF presentation material by clicking on the PowerPoint screenshot on the right-hand side which I have stored on my SkyDrive account.

This video file is approximately 27 minutes long.  I didn’t realize my demonstration was that long, next time I will have to time this out so I can make sure I have plenty of time to show the complete demo and have time to resolve any technical difficulties.  The file is approximately 43MB if you download it and I did make this in HD mp4 quality, so it should be good to watch.  This was my first time using Camtasia to create a screencast, so hopefully everything worked out okay (really sweet product).

Leveraging PowerPivot Demonstration from Dan English on Vimeo.

Hope you enjoy the video, please feel free to post comments and questions.  Later.

Update (4/5/2011):  I have the PowerPoint presentation on SlideShare if you don’t want to download it and just want to view the slides.

Update (4/6/2011):   In regards to the CSV file I simply used the Export wizard in SSMS to create the file.  In order to do that I create the following view in the ContosoRetailDW database to reference:

CREATE VIEW [dbo].[vw_SalesBudgetData]

AS

SELECT DateKey, ChannelKey, StoreKey, ProductKey, SalesQuantityQuota, SalesAmountQuota,

FROM dbo.FactSalesQuota WHERE (ScenarioKey = 2)

When I exported the file I also used the Vertical Bar for the delimiter and included the column headers.

PASSMN May 2010 Follow-up

This past month I presented at our local Minnesota SQL Server User Group.  I was a tad bit exhausted from presenting at TechFuse 2010 and the SQL Server 2008 R2 launch event here in Minneapolis earlier in the month.  It was tough when each presentation was on a different topic and I filled in for the launch event and had less than a week to absorb the content.  I didn’t plan for all of these to occur one week after the other either, just kind of happened.  Needless to say I was a little burnt out, so hopefully my content was good even though I probably sounded a bit less than enthused, which I apologize for.  I also want to apologize for the food.  The Papa Johns pizza was mistaken delivered to Davannis….I wish I could have been there to see that one.

For the evaluations I received the following scores (on a five point scale):

• Effectiveness of the demonstrations – 3.7
• Usefulness of the material presented – 4.0
• Speaker’s presentation skills – 3.5
• Correct blend of presentations and demonstrations – 4.0
• Overall Presentation Rating – 3.8

I have been holding out on posting the content because this was the first month we got an actual recording of the meeting.  I have been working on converting it and making it available to view.  My talk was on SSAS Design Best Practices and Incremental Processing.  I highlighted some of the built-in items in BIDS along with BIDS Helper, did a few demos, and wrapped it up with a little PowerPivot comparison.

PASSMN May 2010 – SSAS Design & Incremental Processing from Dan English on Vimeo.

I wanted to provide some follow-up reference links that goes over some of my content or highlights items that I discussed:

• SQL Server Best Practices
• Designing Effective Aggregation with Analysis Services (video – Chris Webb)
• To Partition or Not – This is the Question
• In Search of Aggregations
• Project REAL: Data Lifecycle – Partitioning
• SSAS – Speed up dimensions using a NULL default cube measure
• Excel 2007 Document: Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables
• SQL Server 2005 Report Packs – SQL Server 2005 Integration Services Log Reports

I think I misspoke a couple of times.  One time I stated that I don’t usually have the security information for SSAS ahead of time.  That isn’t necessarily the case, usually it is a requirement up front.  Sometimes though we add it in after the fact and it doesn’t take a lot of work to accommodate that either using SSAS roles.  I believe I also mentioned that PowerPivot was row based compression and it is column.  It was kind of funny, because the day after my presentation I ran into a blog posting about hierarchies in PowerPivot which I was talking about at the end of my presentation and did a posting in regards to it – Hierarchies in PowerPivot.  You still don’t get the drill path like you would in SSAS, but you can get a similar look-and-feel.

The other big thing was the incremental processing setup I had posted about previously – Analysis Services (SSAS) Processing and Aggregations.  I had originally set that package up to handle yearly incremental processing and before I published it I changed it to monthly.  Well needless to say I forgot to take into account the yearly partitions that might be included and these were getting included in the full processing group instead of being ignored.  I have made the change and have created a new version of the packages and replaced the old ones.

Downloads from my SkyDrive (presentation and SSIS package download):

  

Feel free to leave me any comments if you have any questions or ask me questions at one of the PASSMN monthly meetings.

Did you know? SQL Server 2008 SSRS New Data Visualizations

The new Gauge data region is most often used to provide a high-level summary of your data by highlighting key performance indicator (KPI) values. The gauge uses a pointer to show a single value. You can add a range to highlight a subset of values on your scale and control the size and positioning of the scale on the gauge to create different visual effects.

 

The new Chart data region supports a richer set of data visualization features. The new chart types include bar/column cylinder, pyramid, funnel, polar, radar, stock, candlestick, range column, range bar, smooth area, smooth line, stepped line, and box plot chart types. There is also built-in support for Pareto and Histogram charts.

 

There are many more enhancements that have been made to the charting functionality, so check out the BOL link below and read all about them along with other new features.

Check out a screencast about the new Data Visualizations in SQL Server 2008 here.

Read more about this in the SQL Server 2008 BOL ‘What’s New in Report Authoring’.

Did you know? SQL Server 2008 SSRS New Data Visualizations from Dan English on Vimeo.

Did you know? SQL Server 2008 includes Dimension Designer Enhancements

SQL Server 2008 includes Dimension Designer Enhancements, new attribute relationships designer, and additional Best Practice Alerts:)

The Dimension Wizard within BIDS 2008 (VS 2008) for Analysis Service Projects has been simplified and introduces a new attribute relationship designer.

The wizard has reduced the number of screens and includes an auto-detection for parent-child hierarchies.

There have also been modifications to the Best Practice Alerts and there are now 57 alerts integrated into the designer along with a new option to add comments to them and disable them if desired.

Check out a screencast about the Dimension Designer Enhancements in SQL Server 2008 here.

Read more about this in the SQL Server 2008 BOL ‘Dimension Designer (Analysis Services – Multidimensional Data) ’.

Did you know? SQL Server 2008 includes Dimension Designer Enhancements from Dan English on Vimeo.

PerformancePoint Server – Monitoring & Analytics Tech Night Recording

A couple weeks back I did a Tech Night at the Magenic Minneapolis office.  When I was using the MS BI VPC 6 it froze up on me quite a bit and I am thinking this was because I was using Live Meeting and sharing my desktop because I performed the presentation over the weekend and since then without any issues.  I created a screencast of the entire presentation and uploaded it for everyone to check out to MSN videos (soapbox).  I did the recording five times and picked the best one out of the five.  I would do it a sixth time, but I don’t think I will ever be completely satisfied with the recording, so I finally gave in and uploaded one.

I hope everyone finds it helpful.  I go over an overview of the PPS 2007 product and a demonstration of using the Monitoring & Analytics portion which is based off the example by Kevin Goff, Microsoft MVP, taken from the Mar/Apr 2008 issue of Code magazine here.

PerformancePoint Server 2007 Monitoring & Analytics Overview and Demo from Dan English on Vimeo.

All of the files used for the demonstration can be found here.  The files that are available include the presentation (PDF format), the Entire PSS Download sample files (including some readme instructions — 2 parts to download), and the video if you want to download it (2 parts to download).  I couldn’t upload the video to YouTube because it was too long and apparently you need a director account to perform a large upload.  The presentation was done prior to the release of PPS SP1, so the filter improvements will not be seen in this video.  For information in regards to SP1 or to the download links check out the PerformancePoint Team Blog here.

Did you know? SQL Server 2008 includes Backup Compression

SQL Server 2008 includes database Backup Compression:

Keeping disk-based backups online is expensive and time-consuming. With SQL Server 2008 backup compression, less storage is required to keep backups online, and backups run significantly faster since less disk I/O is required.

By default, backup compression significantly increases CPU usage, which can adversely impact concurrent operations. You can now create low-priority compressed backups in a session whose CPU usage is limited by Resource Governor.

SQL Server 2008 Developer and Enterprise Editions and later supports compressing backups, and every edition of SQL Server 2008 or later can restore a compressed backup.  

· Check out a screencast about the new Backup Compression feature in SQL Server 2008 here.

· Read more about this in the SQL Server 2008 BOL ‘Backup Compression (SQL Server)’.

Did you know? SQL Server 2008 includes Backup Compression from Dan English on Vimeo.

Did you know? SQL Server 2008 includes Auditing

SQL Server 2008 includes Auditing (server and database):

SQL Server 2008 now enables you to configure server and database level audit specifications.  These specifications include server operations such as management changes and database actions such as data manipulation language (DML) and data definition language (DDL) operations.  The output of the audits can be sent to a file, Windows Security event log, or the Windows Application event log.

· Check out a screencast about the new Audit feature in SQL Server 2008 here.

· Read more about this in the SQL Server 2008 BOL ‘Understanding SQL Server Audit’.

Did you know? SQL Server 2008 includes Auditing from Dan English on Vimeo.

Did you know? SQL Server 2008 includes GROUPING SETS

SQL Server 2008 includes GROUPING SETS:

Grouping Sets is an extension to the GROUP BY clause that lets users define multiple groupings in the same query. Grouping Sets produces a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster.

Read more about this in the SQL Server 2008 BOL ‘GROUPING SETS’.

Check out a screencast about the new GROUPING SETS feature in SQL Server 2008 here.

Did you know? SQL Server 2008 includes GROUPING SETS from Dan English on Vimeo.