Recap of a Terrific SQL Rally Event

The May 2012 SQL Rally in Dallas was everything I'd looked forward to. A huge thanks to Sri & all the other organizers.

Tim Mitchell has lots of pictures posted on Flickr.

Sessions I Delivered

I was lucky enough to have two sessions on the schedule, both from community voting. (I still claim there must have been some data quality issues with the community voting data!)

Dashboards…How To Choose a MSBI Tool.  My first session on Thursday was about comparing and contrasting Microsoft BI tools.  It was very much oriented towards if you have a specific requirement, which tool might be most suitable. I was thrilled to have a full house for this session.  Seating in the room was about 100 & a few folks milled around the back too. This session was a great learning experience for me - it was the first time I'd worn a microphone, had more attendees than I'd spoken to previously, and had a creaky platform that I could have lived without. <grin>  I am told one of the SQL Rally goals is to give a speaker like me experience in a bigger venue – I’m quite appreciative!

Managed Self-Service BI and Data As A Service.  My second session was in the dreaded final slot on Friday. Still had about half the room full, so I was quite pleased with that given the time slot. In this session we talked about the Self-Service BI tools in SQL Server 2012 & SharePoint 2010, as well as how to manage the Self-Service environment in SharePoint (with most emphasis on PowerPivot for SharePoint). Since I'd had the luxury of delivering another presentation the previous day, I started this one far more comfortably with respect to the mic & the platform.

Both sessions had tons of interaction with the audience, which makes me have way more fun.  As a speaker, the only thing I wished for that was a wider podium - there wasn't enough width to use my mouse. Other than that very small thing, I thought the venue was very good.

Sessions I Attended

Mark Tabladillo - Social Marketing for Microsoft Professionals

Bill Pearson - Overcoming Barriers & Avoiding Mistakes with BI

Tim Mitchell - Intro to Data Quality Services

Andy Leonard - SSIS 2012 Instrumentation and Logging

Jen Stirrup - Business Intelligence in Azure

Adam Saxton - Digging into SSRS 2012 with SharePoint 2010

Greg Galloway - Making Heads or Tails of SSAS Storage

John Welch - SSIS Performance Tuning

Adam's & Greg's sessions both were highlights for me from a technical perspective. Mark's session gave me some great food for thought on blogging, videos & sharing slides.

The Fun Stuff

The Meet the SQL Professionals event on Wednesday night was so much fun - I can't even begin to list how many new people I met. And of course, the best part was catching up with friends.

Sadly I missed SQL Karaoke in order to work on my VM to make sure it was all good for the next day. I would have been a crazy woman had I waited to get things all fixed up, so I stayed in. As a bonus, I avoided a hangover!

On Friday night I went to dinner with a bunch of people. That was one of the highlights of the trip for me.

So, that's a wrap for SQL Rally. Looking forward to the Summit!

Why is my SSAS Query Log Table Empty?

Overview: Just a quick tip re: resolving an issue with getting SSAS the query logging table populated.

Level:  101

Enabling the SSAS Query Log

In Management Studio, if you right-click the SSAS Server instance, you are presented with Analysis Server properties.  Here is where you tell SSAS to log queries.  The default table name is “OlapQueryLog” which will reside in the database you specify in the connection string.

In the screen shot below, you’ll see I specified a sampling of 1.  This is because I wanted to capture all queries within this test database.  I have a dashboard getting close to being rolled out.  Before migrating to Production I wanted to run the Usage-Based Optimizer for the purpose of creating a few aggregations that’ll help query performance of the new dashboard (particularly a many-to-many bridge table that I know is a bit of a bottleneck).

So, I enabled the QueryLog properties as shown above.  Then I went and ran my dashboard to force some records into the OlapQueryLog table. 

Next, a quick select statement on the OlapQueryLog table – no records.  It was empty.  Huh?

SSAS Service Account Permissions

Why was my OlapQueryLog table empty?  Well, in the connection string I didn’t specify certain credentials.  Which means the SSAS Service Account was responsible for running it.  And … that SSAS Service Account didn’t have write permissions to the database specified in the connection string.

So, since this was a quick exercise, I chose to grant the write permissions needed by the SSAS Service Account & then run my queries; after verifying I had the records I expected, I then set the QueryLogSampling property back to 0 (to turn SSAS query logging back off), and revoked the write privileges. 

If you intend to keep logging enabled for more than a quick test, it would be a better practice to define a specific user ID and password that has few privileges overall, but with the write permissions it needs, and embed it in the QueryLogConnectionString property.

Finding More Information

Technet – Configuring the Analysis Services Query Log

SQL CAT - Reintroducing Usage-Based Optimization in SQL Server 2008 Analysis Services

Getting Data From a Web Service or Application Into PowerPivot

Overview:  Recently I was asked if PowerPivot could get data from a Web service.  This post discusses how you could make happen if an Atom-aware Data Feed is available.

Level: 101

Intro to Data Feeds in PowerPivot

There are several options for importing external data into PowerPivot.  One of these is Data Feeds. 

     

MSDN defines data feeds which are supported by PowerPivot:

“A data feed is an XML Atom 1.0 data payload generated by a Web service, data service, or Web application that supports the Atom standard. A data feed has a consistent XML tabular structure that does not change over time, and variable data that can be different each time the feed is generated.”

Data feeds can be used to gain access to all types of data, inside or outside of a private organization.  Inside an organization, feeds can be one option for providing data to self-service BI users who are not permitted to have direct access to databases.

Working with Data Feeds in PowerPivot

• The Web service or application needs to provide a Data Feed structured in the Atom 1.0 format, for PowerPivot to be able to consume it.
• A URL is made available as the Data Feed.  This can be a simple URL, or a more complex URL which passes parameters (up to the 2,048 character limit). 
• PowerPivot does not support RSS feeds.  This is because the Atom format follows a standard which is more structured & rigid, making it more suitable to working with data.
• By default, Windows authentication is used for importing data into PowerPivot.  Very few exceptions are allowed (or explicitly supported).

Steps to use a Data Feed in PowerPivot

1.  First, find the URL of the data feed you need.  For this example, let’s use the PluralSight Training Catalog found here: http://www.pluralsight-training.net/odata/ 

Note:  This is a consumer sample provided by OData.org.

     

2.  Copy the URL onto your clipboard.

3.  In the PowerPivot window, click the “From Data Feeds” button.  In the Data Feed URL text box, paste the URL from your clipboard.

Test the Connection to ensure it works, then hit Next.

     

4.  Select the Tables you wish to import into PowerPivot and then Finish.  (I chose them all the first time since I hadn’t seen this set of data previously.  If you can, deselect tables you don’t need – reducing the size simplifies your PowerPivot model & improves its performance.)

Hint!  The Filter Details isn’t functional when you’re working with a Data Feed.  If you need to filter the results, it would need to be done by passing parameters within the URL.

     

5.  You can observe the progress as it imports the data into PowerPivot. 

Hint! Extremely large datasets will take some time – that’s our trade-off for ease of use with PowerPivot.

     

6.  And now the data is imported. 

     

Hint! Depending on how the underlying data for the feed was set up, you may or may not get relationships set up for you in PowerPivot.  In this sample dataset, there were no relationships auto-detected by PowerPivot.

     

Data Feeds Supported within the Microsoft Stack

Just to take it a step further … In addition to custom Web services or applications, there are two areas within the Microsoft stack where you can currently use data feeds to import data into PowerPivot:

• SQL Server Reporting Services reports (starting with SQL Server 2008 R2).  Let’s say there’s a Weekly Sales Report by Region, generated by the data warehouse which handles some transformations and data quality issues.  Since this Weekly Sales Report by Region is considered the authority, there’s no need to reinvent the wheel to re-accumulate those numbers.  Subscribing to the authoritative report improves accuracy & consistency, not to mention saves you time when developing the PowerPivot model.  (Remember … it’s one feed per “data region” in the report.)
• Lists in SharePoint 2010.  Let’s say there’s a list which maintains the Salesperson assigned to each geographic area (think of this as some master data maintained in a SharePoint list).  By subscribing to this list, which is considered the authority, the most recent geographic assignments will be retrieved which increases the accuracy & consistency of reports created from the PowerPivot model.

To find the URL to use in a Data Feed, click the “Export to Data Feed” icon in the report toolbar (after the report has been executed) as shown below, or in SharePoint. 

     

Finding More Information

MSDN – Data Feeds Overview (PowerPivot for SharePoint)

IT Conversations – PowerPivot, OData, and the Democratization of Business Intelligence

TechNet – Generating Data Feeds from Reports (Report Builder and SSRS)

What is Data Explorer? And How Does it Compare to PowerPivot?

Overview: When investigating the Discover > Enrich > Publish capabilities of Data Explorer, my immediate question was: “Hmmm…Sounds awfully similar to PowerPivot. How is Data Explorer different?” This entry shares my initial impressions of Data Explorer.

Level: 101

What is Data Explorer?

Data Explorer is a self-service tool to Discover, Enrich (Transform), and Publish datasets. Data Explorer is currently in a SQL Azure Labs “Preview” (i.e., experimental) mode. You can view the datasheet here.

For a business user, the primary purpose of Data Explorer is to improve or eliminate manual processes to consolidate, standardize & cleanse data before it’s consumed by an end-user tool (such as PowerPivot).

For an IT user, the primary benefit of Data Explorer is likely to be the capability to publish the results as an OData feed for consumption by another application.

Discovering Data:

   

Enriching Data:

   

   

Publishing Data:

   

   

A Few Differences Between Data Explorer and PowerPivot

Discovering Data:

• Data Explorer has functionality to “suggest” additional data sources for you (from Azure Marketplace or Bing) based upon the data you already have (i.e., if it detects a phone number or an address in your dataset).  While both tools are similar in that they facilitate bringing together disparate data sources, PowerPivot does not attempt to make suggestions.  Data Explorer places more emphasis on finding & bringing in additional related data from the web.
• Data Explorer can extract data from some interesting data sources not available to PowerPivot, such as Web Content (lists & tables), Office 365 including SharePoint Online, or JSON documents.
• For relational data, Data Explorer can currently connect only to SQL Server or SQL Azure.  PowerPivot can natively connect to many more relational data sources.
• Data Explorer cannot connect to Analysis Services from what I can tell (whereas PowerPivot can).

Enriching Data:

• Data Explorer does have some nice capabilities to allow a non-technical user to perform transforms on the data (nowhere near the capabilities of SSIS though – don’t forget Data Explorer is intended as a self-service tool). Some transformations might be easier to do in the Data Explorer interface than in PowerPivot.  It also provides a way to centralize the transformations, if the dataset will be consumed by multiple downstream applications.

Publishing Data:

• Publishing data is where Data Explorer really distinguishes itself – really, the main purpose of Data Explorer is to publish data so it can be consumed downstream by another tool.  Conversely, PowerPivot’s main purpose is to support data visualization rather than publish it outside of the Excel (.xlsx) file it resides in.
• The ability to publish OData feeds via Data Explorer is, perhaps, its biggest benefit.  Publishing data as a feed allows other applications to consume it (including PowerPivot).
• Currently only the cloud version has publishing capabilities (the Data Explorer team is working on this capability for the desktop add-in).

Other:

• Data Explorer has its own Formula Language called “M” (um, yeah, we were hoping for another language to learn…I just had to say it).
• Data Explorer has a cloud version, and a desktop version which works as an add-in to Excel (both 32-bit and 64-bit downloads of the Excel add-in are available). It may be easier to access on-premise company data behind a firewall using the desktop client version of Data Explorer.  (PowerPivot is currently only available as an Excel add-in; no word on when it might be available as part of Office 365.)
• The cloud version of Data Explorer uses Silverlight.

Finding More Information

MSDN – Learn More about Microsoft Codename “Data Explorer”

MSDN – Data Explorer – Getting Started

Chris Webb’s Blog – Self-Service ETL with Data Explorer

MSDN Magazine Article by Julie Lerman – Let Power Users Create Their Own OData Feeds

Jamie Thomson’s Blog – Thoughts on Data Explorer

Jamie Thomson’s Blog – Data Explorer Feedback Part 1

Data Explorer Team Blog

Oakleaf Systems Blog – Mashup Big Data with Microsoft Codename “Data Explorer” – an Illustrated Tutorial

Managing Data Sources in a PowerPivot Gallery

Overview:  A quick tip about how to manage data source connections within a SharePoint PowerPivot Gallery.

Level:  101

The PowerPivot Gallery

The PowerPivot Gallery is a specific type of SharePoint document library, intended for storing & sharing PowerPivot workbooks as well as BISM connections to tabular models.  The PowerPivot Gallery is also well suited for storing self-service reports from Power View and Report Builder.  Silverlight allows the PowerPivot Gallery to be more visually oriented than a standard document library.

The default view for a PowerPivot Gallery is called the “Gallery” view, as shown below.  There’s also a Theater and a Carousel view.

Where to Manage Data Sources

If you want to double check what data source a report is using, or change the data source, you find that under the “All Documents” Library View.  From the Library ribbon menu, select the Current View drop-down and change it to All Documents:

Now we see the more typical document library layout, and the “Manage Data Sources” menu item is available:

The “Manage Data Sources” menu item will be visible for users with Contribute rights, but not for View only rights.

Resolution for the SSAS FileStore Error When Processing Dimensions

This week I deployed an updated SQL Server Analysis Services (SSAS) database from Dev to Test.  When processing in Test, I got an error which we had not experienced in the Development environment.

The dreaded File system error “A FileStore error from WriteFile occurred.”

File system error: A FileStore error from WriteFile occurred. Physical file: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Temp\MSMDCacheRowset_1136_e48_dnogks.tmp. Logical file: . : The parameter is incorrect. .

After an audible groan, I did what any good BI developer does.  Opened my web browser.  I won’t repeat the common resolutions I found since none of them applied to my situation.  (Sidenote:  it was not the string limitation issue which, incidentally, is fixed in SQL Server 2012.)

What’s The Fix?

One of my teammates noticed the versions were different between Dev and Test.  As it turns out, the Development SQL Server box had Service Pack 1 for SQL Server 2008 R2 installed whereas the Test box did not.  So I promptly gave the client DBA an opportunity to be a hero and get SP1 installed.  Next time I processed the cube, the issue was gone.

To download SP1:  Download Center