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!

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

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

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.

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

A big thanks:  First thing first.  I need to thank my good friend Javier Guillen (Blog | Twitter) for proofreading the decision matrix below & providing additional input.  If you are interested in PowerPivot or Analysis Services, be sure to follow Javier.

Overview: I’ve been learning about the new features coming out in SQL Server 2012, including Tabular Models & PowerPivot.  One thing I wanted was a decision matrix – for a new project, which type of model is most suitable for the requirements I am presented?  I wanted thoroughly understand why I might suggest to a client that we create a model other than traditional OLAP.  So, I created the decision matrix below for the purpose of helping to decide which type of model to create in SQL Server 2012.

Level:  101

Versions applicable:

• PowerPivot 2.0
• SharePoint 2010
• SQL Server 2012 (initial release)

What is BISM?

In SQL Server 2012, the concept of the BI Semantic Model (BISM) is introduced.  It includes:

• PowerPivot for Excel
• PowerPivot for SharePoint
• Analysis Services Tabular
• Analysis Services Multidimensional

You can think of PowerPivot and SSAS as a graduating lifecycle of tools from Personal BI to Team BI to Corporate BI.  This is a really exciting step forward in the Microsoft BI toolset – one that I’m personally very excited about because it allows Personal BI agility + the ability for a solution to mature over time.

Currently, PowerPivot and SSAS Tabular are similar structures "under the covers" and have a seamless upgrade path.  However, SSAS Multidimensional is a completely different structure - I have no doubt further integration will develop over time.

Decision Matrix:  How to Choose Which Type of Model Meets Your Needs?

There are lots of decision factors here.  As the products evolve and mature, these factors will certainly get refined.

Finding More Information

Marco Russo’s blog – Why to Use Tabular in Analysis Services 2012

Analysis Services and PowerPivot Team Blog – Comparing Analysis Services and PowerPivot

Cathy Dumas’ MSDN blog – When to Choose Tabular Models over PowerPivot Models    <—really useful

Javier Guillen’s blog – Observations on Interoperability Between BISM Tabular and OLAP clients

Chris Webb’s blog – So, What is the BI Semantic Model? 

Sample Chapter from Teo Lachev’s Book: Chapter 1 – Introducing Business Intelligence Semantic Model

Simran Jindal’s blog – So What is the BI Semantic Model or BISM Really?

SQL Rally 2012, in Dallas, is coming up in May.  I am honored (and simply shocked) to have two sessions in the lineup.  Both were community picks – how amazing is that?  A huge thanks for everyone who voted.  And, a big thanks to the organizers who I’d imagine are putting in many hours to organize an event of this size.

My Two BI Sessions at SQL Rally

The two sessions I have on the schedule are:

• “Managed Self-Service BI” and “Data as a Service” 
• Dashboards…How to Choose an MSBI Tool

I’m particularly excited about my newest session on Self-Service BI.  It’s a topic near & dear to me since I’m a reformed accountant turned techie.

Full List of BI Sessions at SQL Rally

There’s tons of great sessions across all the areas.  Since I’m a BI kind of gal, I can’t help but focus on the BI-related sessions.  Here’s a list, grouped by topic:

Azure

• Business Intelligence in Azure: SSIS & SSRS Focus - Jen Stirrup (Copper Blue Business Intelligence)

Big Data

• Fitting Hadoop into your Enterprise BI Strategy - Cindy Gross (Microsoft)

Data Quality Services

• Introduction to Data Quality Services - Tim Mitchell (Artis Consulting)

Integration Services

• SSIS Performance Tuning - John Welch (Pragmatic Works)
• Adapting your ETL Solutions to use SSIS 2012 - Devin Knight (Pragmatic Works)
• SSIS 2012 - Instrumentation and Logging - Andy Leonard (Linchpin People, LLC)
• Got a Data Mess the Size of Texas? Data Cleansing Using the "Scrubbing Bubbles" of SSIS - Amy Lewis (Statera)

Reporting Services

• The Report Part Library: Increasing Development Productivity in Reporting Services - Jessica Moss (CapTech Consulting)
• Digging into Reporting Services 2012 with SharePoint 2010 - Adam Saxton (Microsoft CSS)

Tabular Model, SSAS, Data Mining

• Developing and Managing a BI Semantic Tabular Model in SQL Server 2012 Analysis Services - Patrick LeBlanc (Microsoft)
• DirectQuery vs VertiPaq (cached) mode in Tabular Model (SSAS) - Julie Koesmarno (Independent Consultant)
• Making Heads or Tails of Analysis Services Storage - Greg Galloway (Artis Consulting)
• Tuning Analysis Services Performance - John Welch (Pragmatic Works)
• Zero to Cube - Fast Track to Analytics - Adam Jorgensen & Dustin Ryan (Pragmatic Works)
• Building a Write-Back Application with Analysis Services - Bryan Smith (Microsoft)
• Data Mining (It's not the size of your data - it's what you do with it) - Adam Jorgensen & Devin Knight (Pragmatic Works)

Misc BI and Data Warehousing

• Overcoming Barriers and Avoiding Mistakes with BI - William E Pearson III (Island Technologies Inc.)
• Data Warehouse Mistakes You Can’t Afford to Make - David Stein (WaveTwo )
• Using Columnstore Indexes in SQL Server 2012 - Carlos Bossy (Quanta Intelligence)
• SQL Server 2012 T-SQL - Kathi Kellenberger (Microsoft Corporation)
• “Managed Self-Service BI” and “Data as a Service” – Melissa Coates (Intellinet)
• Dashboards…How to Choose Which MSBI Tool – Melissa Coates (Intellinet)

I’m really looking forward to this event in May.  Check out the full Agenda for all 60 General Sessions, & the Pre-Conference Sessions.  See y’all at SQL Rally!

Summary:  A quick workaround for an intermittent SQL Server Reporting Services error.

Level:  101

Reporting Services Error

The error I received is:

Could not update a list of fields for the query.  Verify that you can connect to the data source and that your query syntax is correct.

I received this error sometimes after updating the dataset (with the preview of the query running fine), at the point when I clicked OK to save the dataset changes.  In my situation the data source was an Analysis Services cube (SQL Server 2008 R2).

Workaround

Since I started using this method, I haven’t seen the error.

1.  Go to Dataset Properties.  (Specifically…do not  jump directly to the Query.)

2.  Select the Query Designer button.  (Yes, it’s an extra click, I know.)

3.  Update your query.  Click OK within the Query Designer.

4.  Click the Refresh Fields button, and then OK.

That’s it.  I haven’t had any trouble since I started accessing and saving the query “the long way” and now it’s just habit.

Overview: Some things to consider when deciding if you want to integrate new data within an existing cube (and, optionally, use a perspective) or create a new separate cube.

Level:  201

New Data Mart

Recently we created a new data mart (subject area) within an existing data warehouse.  The new data mart had several new facts, several new dimensions, as well as relationships to existing (conformed) dimensions such as Date & Customer.  I had more new dimensions than usage of conformed dimensions.

Using the existing SSAS database project wasn’t up for discussion – we need to reuse the conformed dimensions after all.  I also used the same Data Source View (DSV).  What wasn’t so clear immediately was whether to create a new cube for the subject area, or use the existing cube.  A perspective in the existing cube could certainly help simplify things, but we actually chose to create a new cube.  Here’s why…

Reasons We Chose to Create a New SSAS Cube

This isn’t an exhaustive list, but it’s the things that were meaningful for our project.  Be sure to check out Chris Webb’s blog entry for more things to consider (like security).

• We were dealing with a separate data mart – the new subject area had no overlap between measure groups & no expected need to do cross-analysis of existing measure groups with the new measure groups.  Put another way, we expect the reporting & analysis to be independent.  (Although linked measure groups are not ideal, that is in our back pocket if the need should come up in the future.)
• Shield the existing cube from any data quality issue / cube processing errors we may experience during the early going (in our situation, the data mart was based upon a brand new Oracle module which was heavily customized & not yet in Production itself).
• Less regression testing upon deployment (we were on a very short timeline).
• The existing cube was already a bit large.
• Potential for improved query performance (we expected a high volume of data).
• Facilitates multiple developers in the SSAS BIDS environment (this is a big deal in a team environment).
• Allows for a separate Agent job to process the dimensions and the measure groups (although we do still have a dependency on the conformed dimensions).  This flexibility is a good thing in our situation, since it’s a different subject area which can be run faster on its own, and on an independent schedule.
• Simplification for end users, without the need for a perspective.

Any additional thoughts or differences of opinion?  Leave me a comment!

Finding More Information

Chris Webb’s blog – One Cube vs Multiple Cubes

Overview:  A quick tip re: getting the underlying data model correct when a bridge table is involved, to avoid errors in SQL Server Analysis Services.

Level:  301 (assumes familiarity with bridge tables, as well as SSAS Dimension Usage relationships)

SSAS Error Received

When you have a complex set of data being modeled for addition into the data warehouse, sometimes the star schema joins are not as simple as we’d like them to be.  My first choice is always to join facts to dimensions at the surrogate key level (i.e., the lowest level of granularity).  However, recently we had a challenging set of data where we needed to model the data a little differently.  What we had done in the relational tables (i.e., the underlying data warehouse) was rejected by SSAS.

The SSAS deployment error:

Errors in the metadata manager.  The ‘Dimension 2’ many-to-many dimension in the ‘Fact 1’ measure group requires that the granularity of the ‘Dimension 1’ dimension is lower than that of the ‘Many-To-Many Bridge’ measure group.

The Underlying Data Model

To avoid the error shown above, Join A needs to be at a lower level of granularity than Join B:

If Join A is at the surrogate key level (i.e., the level of granularity the Dimension 1 records are stored at), you shouldn’t run into this issue. 

Why wouldn’t Join A be at the surrogate key level, you ask?  SSAS does let you create a relationship using a non-key granularity attribute (i.e., using a join that’s higher than the lowest level of detail).  A common example of this is joining on the non-key attribute of Quarter when your Date dimension is stored at the day level, because the facts aren’t available at the day level. 

SSAS Dimension Usage

The star schema shown above translates to the following relationships in the SSAS Dimension Usage: