Dan English's BI Blog

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

Archive for November, 2012

PASSMN Special December 2012 Meeting

Posted by denglishbi on November 29, 2012

Next week the Minnesota SQL Server User Group is having a special meeting on Wednesday, December 5.  The presentation will be on Big Data.  Be sure to register so that your name badge will be available for you at the Microsoft Technology Center when you arrive.Minnesota SQL Server Users Group

The sponsor for this special meeting is Microsoft Technology Communities.

image

Location: 3601 West 76th Street, Suite 600 Edina, MN 55437

Date/Time: Wednesday, December 5 from 5:30 to 7:00 p.m.

Agenda

  • 5:30 – 5:45 : Registration / hospitality / networking / food!
  • 5:45 – 6:45 : Hadoop’s Role in a Big Data Architecture
  • 6:45 – 7:00 : Announcements / Networking

Please click here for meeting details and to RSVP for the event

Presentations

Jim Walker talks Hadoop, Big Data and Microsoft Tools

Jim Walker, Hortonworks, is a recovering developer, professional marketer and amateur photographer with nearly twenty years experience building products and developing emerging technologies. During his career, he has brought multiple products to market in a variety of fields, including data loss prevention, master data management and now big data. At Hortonworks, Jim is focused on accelerating the development and adoption of Apache Hadoop.

And don’t forget, the SQL Lunch that was scheduled for October was rescheduled and will be next month for Friday, December 14 from 11:30 to 1:30 with me as the host – SQLFriends Lunch–Dec 14.

Posted in Training | Tagged: , , | Leave a Comment »

Minnesota Microsoft BI User Group 2012 Q4 Meeting–Dec 4

Posted by denglishbi on November 21, 2012

The next MN Microsoft Business Intelligence User Group is coming up on Tuesday, December 4. This meeting we will have two sessions starting out with a talk on Data Mining and then a presentation about new Business Intelligence features in Office 2013, SharePoint 2013, and SQL Server 2012 SP1. Just a reminder that the location for this event is at the Microsoft Technology Center location in Edina.  Please register so that we can have your name badge pre-printed.

MN Microsoft BI User Group 2012 Q4 Meetingimage

Sponsored by:

MSFT_logo_Page

Date: Tuesday, December 4, 2012

Check In: 2:30 PM Event Time: 3:00 PM – 6:00 PM

Location: Microsoft Technology Center – 3601 76th St W, Suite 600 Edina MN 55435 (in LOW 1, 2, and 3)

To join via Live Meeting click here.

Please Register Now (Please register so that we can have your name badge pre-printed)

Agenda:

  • 2:30-3:00 : Registration
  • 3:00-3:15 : Microsoft BI User Group Updates & Announcements
  • 3:15-4:15 : Data Mining with the Tools You Already Have (Steve Hughes)
  • 4:15-4:30 : Break
  • 4:30-5:30 : What’s New in Business Intelligence in Office 2013, SharePoint Server 2013, and SQL Server 2012 SP1 (Jin Cho)
  • 5:30-6:00 : Social and Networking

Presentations:

Data Mining with the Tools You Already Have – This session will cover data mining and using predictive analytics with tools you already use. We will cover using both Excel and Analysis Services to use data mining to make your data work for you. We will cover some of the commonly used algorithms built right into the tools such as Decision Trees, Clustering, and Neural Networking. We will explain use cases for the algorithms as well as demo the fun you can have exploring and forecasting with your data.

Steve Hughes (@DataOnWheels) is a Practice Lead at Magenic. His area of expertise is in data and business intelligence architecture on the Microsoft SQL Server platform. He was also the data architect for a SaaS company which delivered a transportation management solution for fleets across the United States. Steve has co-authored two books and delivered more than 30 presentations on SQL Server and data architecture over the past six years.  He also provides insights from the field on his blog at http://www.dataonwheels.com.

What’s New in Business Intelligence in Office 2013, SharePoint Server 2013, and SQL Server 2012 Service Pack 1 – Come and find out how the Office 2013 and SharePoint Server 2013 integrated analytics platform that allows your organization to discover and share business insights in dynamic environments.  We will provide demos of the new Business Intelligence capabilities from the Office 2013, SharePoint 2013 and SQL Server 2012 SP1. 

Jin Cho, Microsoft Business Intelligence Technology Specialist, evangelizes on Business Intelligence capabilities for suite of Microsoft products for the last 4 years.  Jin has proven ability to deliver Business intelligence solutions utilizing leading market BI technologies.  Prior to becoming a Microsoft employee, Jin spent 11 years at Allina Health System.  He led the Business Intelligence/Data Warehouse team on an effort to provide clinical, financial, and operational BI solutions.  He was also responsible for implementing reporting and analytic environment for the Epic electronic medical record system.  Jin has bachelor degree in Statistics from the University of Minnesota.

Please Register Now (Please register so that we can have your name badge pre-printed)

Hope to see you there!

Posted in Business Intelligence, Training | Tagged: , | Leave a Comment »

Book Review: DAX FORMULAS for PowerPivot: The Excel Pro’s Guide to Mastering DAX

Posted by denglishbi on November 15, 2012

This week I got the opportunity to read a new book out on the market by Rob Collie (PowerPivotPro) – “DAX FORMULAS for PowerPivot: The Excel Pro’s Guide to Mastering DAX”.  I have to admit that once I received the book I read through it in a couple of days, couldn’t put it down and wanted to finish it.  I really enjoyed the style of the book, just like Rob’s blog posts, and there was just a really good natural transition of the content that it kept me hooked.  While reading the book I really felt that I was in a classroom and Rob was presenting the material, something about the writing style, really enjoyed it (hat tip to Rob).

The book is loaded with great tips and advice from Rob, reference links to his blog posts and other content, and real world examples.  The solutions and examples provided in the book can be used right away if you are already using PowerPivot or Tabular SSAS.  There is material in here not just for the Excel pros, but also for IT/BI pros as well.

The second night I was reading the book my son took a peek at what I was doing on my Kindle Fire HD (great reading experience by the way – color, access to the embedded pictures to enlarge, ability to access the links directly, easily take notes and add bookmarks).  My son is in first grade and really starting to get comfortable reading.  He started to read the page I was on:)  It was really cute.  The book was not only good for me, but helped my son learn new words and work on his reading (not sure if this book can qualify for an AR test though)!

When it comes to the editing of the book I did come across a some minor typos, nothing too major, and one figure was missing (Figure 154).  There are references to the Measure Grid (actually Calculation Area in PowerPivot, Measure Grid in Tabular SSAS – this is one thing that I wish Microsoft kept the same or consistent for referencing).  I got a laugh when Rob states that implicit measures are ‘dead to me’ and that he never, ever, EVER creates implicit measures.

The book is really invaluable and the price is a steal (particularly if you buy it on Mr. Excel’s store here)  The length was exactly what I like, short and to the point, I don’t want to read a 800 page door stopper, anything in the 300 or less page range is perfect (that is why I liked the Rational Guide books by Rational Press) and supplemented with links for additional references and more content if desired.  The material and insights that Rob covers is fantastic.  The disconnected table concepts as well as performance tips are PRICELESS.  I would highly recommend this book and without a doubt give it a five star rating.

image

Posted in PowerPivot | Tagged: , , , | Leave a Comment »

PASSMN November 2012 Meeting

Posted by denglishbi on November 14, 2012

The next Minnesota SQL Server User Group meeting is next Tuesday on November 20.  This month the presentation will be on SQL Server database locking and blocking.  Be sure to register so that your name badge will be available for you at the Microsoft Technology Center when you arrive.Minnesota SQL Server Users Group
 
The sponsor for this month’s meeting is Dell Compellent.

Compellent

Location: 3601 West 76th Street, Suite 600 Edina, MN 55437

Date/Time: Tuesday, November 20 from 4 to 5:30 p.m.

Live Meeting:
• URL: https://www.livemeeting.com/cc/usergroups/join?id=8DFJJB&role=attend&pw=Z-%3B%7D*t4%2Cn
• Meeting ID: 8DFJJB
• Entry Code: Z-;}*t4,n

Agenda

  • 4:00 – 4:10 : Registration / hospitality / networking
  • 4:10 – 4:15 : From our Sponsor – Overview of Dell Compellent SAN
  • 4:15 – 5:15 : Introduction to SQL Server Locking and Blocking
  • 5:15 – 5:30 : Announcements and prize giveaways

Please click here for meeting details and to RSVP for the event 

Presentations

Introduction to SQL Server Locking and Blocking (Kirby Richter, Dell Compellent)

What is locking?  What is blocking?  Why should you care?  You want to update a million rows on production, you need to know what type of locks will happen so you can plan accordingly!

You are tuning a query and the normal duration is 1 second but recently it’s been taking over a minute.  Is your process being blocked?

Let’s face it blocking is very common and as a good DBA you need to recognize when blocking is an issue and how to deal with it.  This presentation will cover most of the gory details and includes several fun demos.  You will learn how to check and monitor locking and blocking and how to deal with common issues.

And don’t forget, the SQL Lunch that was scheduled for October was rescheduled and will be next month for Friday, December 14 from 11:30 to 1:30 with me as the host – SQLFriends Lunch–Dec 14.

Posted in Training | Tagged: , , | Leave a Comment »

Reporting Services SharePoint Large Parameter List Issue & Fix

Posted by denglishbi on November 8, 2012

UPDATE (11/13/2012): Additional modification needed to provide ability to manage the parameters in SharePoint (see the bottom of the post for the updates)

If you are using Reporting Services (SSRS) in SharePoint Integrated Mode (I saw this issue with SSRS 2008 R2 SP2 & SSRS 2012 with SharePoint 2010 running with SP1 CU Aug 2012), then something you might want to be aware of.  If you ever have to load a parameter list with lots of records (say 35K+) then you might end up receiving a message like this:image

An error (Unable to read data from the transport connection: The connection was closed.) occurred while transmitting data over the HTTP channel.
An error (Unable to read data from the transport connection: The connection was closed.) occurred while transmitting data over the HTTP channel.
Unable to read data from the transport connection: The connection was closed.

 

If you are running the report in BIDS or SSDT or Reporting Services Native Mode you will be fine, just an issue with SharePoint Integrated Mode.  I also noticed this with a report that had approximately nine parameters (four were hidden) and two of them had approximately 10K records.  So I think it was the combination of the parameters along with some default values getting set that caused the issue.

REPRO: For a quick example of this issue you can easily replicate this with populating a parameter with the FactFinanceKey from the AdventureWorksDW2012 database.   Deploy the report to SharePoint and try and run it:)

I have filed a Microsoft Connect Bug for this here – Reporting Services (SSRS) SharePoint Large Parameter Issue

Well after a bunch of searching and digging through the forums, blogs, BOL, and additional documentation (and even tried an old ProClarity fix) I found the fix for the issue – MaxItemsInObjectGraph….yes, that was a setting that I had to incorporate into a couple of the configuration files for Reporting in SharePoint:S

The first forum posting that pointed me to the web.config and client.config files was Issue Exporting SSRS 2012 Report to CSV file in SharePoint 2010 (MaxReceivedMessageSize property exceeded) and then after doing some additional research running live log tracing on SharePoint I tracked down that it was really related to the WCF and I came across this additional MaxItemsInObjectGraph setting in quit a few posts such as this one MaxItemsInObjectGraph and keeping references when serializing in WCF.

Now I didn’t find the magic post that just told me what to do, but with some trial-and-error I came up with the following steps to fix this:

1. Edit the web.config file in the following location C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\Reporting

NOTE: prior to modifying a config file, always make a backup of the file first and use a text editor like notepad, notepad++ to modify the file, do not use a rich text editor.

2. In the web.config file find the <behaviors> section and make the following addition:

    <behaviors>
      <serviceBehaviors>
        <!–
            Notes:
             1) maxConcurrentCalls is set to 16(default value in .Net3.5)*Processor Count(assuming 32)
             2) maxConcurrentInstances is set to 512 since InstanceContextMode is PerCall
             3) These settings can be removed after upgrading to .Net 4.0
         –>
        <behavior name=”RSExecutionBehavior”>
        <serviceThrottling maxConcurrentCalls=”512″ maxConcurrentInstances=”512″ />
        <serviceMetadata httpGetEnabled=”true” />
        <serviceDebug includeExceptionDetailInFaults=”true” />
        <dataContractSerializer maxItemsInObjectGraph=”2147483647″ />

        </behavior>
      </serviceBehaviors>
    </behaviors>

3. Save the file and then go and edit the client.config file, remember to make a backup copy prior to modifying it.  This file is located in the following location C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebClients\Reporting\

4. In the client.config file add the following section below the <System.ServiceModel> tag and before the <client> tag

  <system.serviceModel>
    <behaviors>
      <endpointBehaviors>
        <behavior name=”RSExecutionBehavior”>
      <dataContractSerializer maxItemsInObjectGraph=”2147483647″ />
        </behavior>
      </endpointBehaviors>
    </behaviors>

    <client>

5. Now I also modified four of the endpoint settings in the <client> section to reference the new behavior that I added (IReportExecution and IReportStreamingAsyncClient):

<endpoint name=”https” contract=”Microsoft.ReportingServices.ServiceContract.IReportExecution” binding=”customBinding” bindingConfiguration=”https” behaviorConfiguration=”RSExecutionBehavior”/>
<endpoint name=”http” contract=”Microsoft.ReportingServices.ServiceContract.IReportExecution” binding=”customBinding” bindingConfiguration=”http”  behaviorConfiguration=”RSExecutionBehavior”/>

<endpoint name=”https” contract=”Microsoft.ReportingServices.ServiceContract.IReportStreamingAsyncClient” binding=”customBinding” bindingConfiguration=”httpsStreaming”  behaviorConfiguration=”RSExecutionBehavior”/>
<endpoint name=”http” contract=”Microsoft.ReportingServices.ServiceContract.IReportStreamingAsyncClient” binding=”customBinding” bindingConfiguration=”httpStreaming”  behaviorConfiguration=”RSExecutionBehavior”/>

6. Once that was done I had to restart the server where the Reporting Services application was running.  The web.config settings seemed to work right away, but not the client.config settings.  Once it restarted I warmed up SharePoint and got SSRS running again.  I tested out my report and it started up just fine, no error message:)

image

And that is the fix, can’t believe this is the first time I have come across this issue, but now there is a fix and it is documented:D  Just a word of caution, this still does not perform as well as it does in SSRS Native Mode, but at least it does work now.  It appears that it takes maybe twice as long to load in SharePoint as it does through the Report Manger (your mileage may vary though).

I have filed a Microsoft Connect Bug for this here – Reporting Services (SSRS) SharePoint Large Parameter Issue

UPDATE (11/13/2012): Additional modification needed to provide ability to manage the parameters in SharePoint -

One quick update on this – there is one more additional service & endpoint that needs to be updated in the config files as well. The reason is so that you can manage the parameters in SharePoint if needed.

Web: <service name=”Microsoft.ReportingServices.ServiceRuntime.ReportServiceManagement” behaviorConfiguration=”RSExecutionBehavior”>

Client: <endpoint name=”https” contract=”Microsoft.ReportingServices.ServiceContract.IReportServiceManagement” binding=”customBinding” bindingConfiguration=”https” behaviorConfiguration=”RSExecutionBehavior”/>

I added the behaviorConfiguration reference. Not only did I update this in the web.config file, I also updated the endpoints in the client.config file as well. It seems like it picked up on the change without a reboot this time as well, so the web.config setting seemed to work, but wanted to keep things in synch so I updated the client.config as well. This time though I did receive a valid SharePoint error message and was able to look it up in the logs to pinpoint the additional service being referenced for this operation. There are two other items in the config file – ReportServiceBackgroundProcessing and AlertManagement – and I have not had to alter these ones yet.

NOTE: Also, in a multi-server environment I had to update the client.config file on the WFE server (web.config did not exist since it wasn’t the application server) and restart that one to get the reports to behave consistently.

Posted in Reporting Services, SharePoint | Tagged: , | 4 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 89 other followers