Tips for Getting Around Charlotte at PASS Summit

Headed to Charlotte for the PASS Summit in October?  We locals are super excited to have over 4,000 pros arrive in Charlotte!  In addition to the Summit being held October 16-18, the Charlotte BI Group is hosting its 2nd annual SQL Saturday on October 19th.  Below is some information about Charlotte to get you started.

The Lay of the Land

Charlotte is considered a “hub and spoke” city, meaning the downtown area is in the middle & several major roads run outwards from the center – although it’s not actually called downtown; it’s usually referred to as Uptown, or sometimes Center City.  The convention center where the Summit will be held is in Uptown.  I’ve lived in Charlotte just over 3 years now, and I still find that Charlotte can be a bit tough to get around - it’s not a grid layout and street names change a lot (and I mean a lot!).  Here’s a high level map of the Charlotte area:

    

Charlotte isn’t a huge metropolis, which I actually quite like.  Population of Charlotte is just over 750,000 (with just over 2.2 million if you count the entire metro area).  There’s not too many suburbs; most of the region is referred to as Charlotte.  We are known as the “Queen City” after the British Queen Charlotte Sophia.  Although Charlotte isn't immensely large, we are large enough to have an NFL team (the Carolina Panthers) and an NBA team (the Charlotte Bobcats).  And, of course, Charlotte is well known for its Nascar presence (I even admit to having been to a race...once).

Getting from the Airport to Uptown

Charlotte Douglas International Airport (CLT) is a great little airport.  Personally I’ve always gotten in and out of there very quickly. 

Driving Directions:  From the airport to the Convention Center in Uptown is about 7 miles.   

Taxi:  A taxi should be about $25 from the airport to Uptown (Center City).  Ground Transportation at the airport is just outside of Baggage Claim.

Airport Sprinter Bus:  If you are game for saving a few bucks, you could hop onto a CATS hybrid-electric Sprinter Bus and get to Uptown for $2 (this will be like a regular city bus because the light rail doesn’t run to the airport yet).  The Sprinter Bus runs every 20-30 minutes.  If your hotel is in Uptown, hopefully one of the stops along Trade Street will be fairly near your hotel.  You can transfer to the Lynx light rail at the CTC if needed.

    

Fun Stuff To Do Near Uptown

The Green.  A small park that I just adore walking through when I’m in Uptown.  It’s a couple of blocks away from the Convention Center near 1st Street between Tryon and College.  There’s different artwork, a lot of it with literary references (I look smarter in the pic just by being there don't ya think?).

NC Music Factory.  The NC Music Factory is a hip & cool place in Uptown which has bars, restaurants, comedy, and music.  It’s in Uptown just off 12th Street (hint:  take a cab over there rather than walking since it’s on the fringes of Uptown).

EpiCentre.  The EpiCentre, located in Uptown at College and Trade, has bars, restaurants, shops, a movie theater and a bowling alley.  Definitely a fun place.  This is also where you can find Whiskey River (the restaurant owned by Dale Earnhardt, Jr.)

There’s also a few trendy little neighborhoods just outside of Uptown if you have some time to explore:  Noda, Dilworth, and Plaza-Midwood.  They each have their share of eclectic dining, arts, and nightlife.

For other attractions in Charlotte, check out the Charlotte’s Got A Lot site.

Links with Helpful Information

PASS Summit 2013 – Travel and Accommodations

Convention & Visitors Bureau – Charlotte’s Got A Lot

Charlotte Center City Partners – Maps

Charlotte’s Got A Lot - Map of Center City

Charlotte Convention Center – Directions and Parking

Charlotte Douglas International Airport – Ground Transportation

Charlotte Area Transit System (CATS) – Routes & Schedules (Choose “5 – Airport” from the drop-down menu)

Lynx Charlotte - Lynx Light Rail

Refreshing an SSRS Snapshot Using T-SQL

Overview: Quick tip about using T-SQL in an Agent Job to refresh a SQL Server Reporting Services snapshot report, rather than a schedule.

SSRS has the capability to schedule the refresh of report snapshots via a report-specific schedule or a shared schedule.  However, what if you don’t have a specific time you want the refresh to run?  Alternatively, what if you want the snapshot to be refreshed after an event occurs, such as ETL completion?

The first step is finding the ReportID (aka ItemID) assigned to the report by ReportServer.  The following query will return several pieces of information, including ReportID (aka ItemID):

SELECT
  NameOfReport = Cat.Name
  ,Cat.Path
  ,ReportID = Cat.ItemID
  ,NameOfAgentJob = Sched.ScheduleID
  ,LastExecutionTime = Cat.ExecutionTime

FROM ReportServer.dbo.Catalog Cat WITH(NOLOCK)

LEFT JOIN ReportServer.dbo.ReportSchedule Sched WITH(NOLOCK)
  ON Cat.ItemID = Sched.ReportID

WHERE Cat.Name = '<InsertReportNameHere>'

   

Now that you have the ID for the specific snapshot report you need to get refreshed, insert that ID for the EventData parameter in the following T-SQL statement:

exec [ReportServer].dbo.AddEvent @EventType='ReportExecutionUpdateSchedule', @EventData='<InsertReportIDHere>'

Using this technique, you could add one or more T-SQL step(s) to kick off the refresh of subscriptions after ETL completes (or whatever other event you wish to trigger the refresh). 

   

If you are controlling refreshes via an Agent job step, you probably also want to make sure the report-specific schedule is set to “Once” or turn it off completely.  If you leave the schedule active (such as the “Once” option shown below), you will still have an Agent Job present that is associated to this schedule (same as how subscriptions are handled).  The name of this Agent job can be found by referring to the Sched.ScheduleID field in the query above.

   

SSRS Report Execution Snapshot vs. a Report History Snapshot

Overview:  An introduction to using snapshots in SQL Server Reporting Services, including an explanation of the differences between a “Report Execution Snapshot” and a “Report History Snapshot.”

Intro to the Ways Users Can Run Reports in Reporting Services

1.  On Demand.  Most reports typically are executed on demand – i.e., the query in the dataset runs and the report is displayed at the time the report is requested by the user.  This is the default in “Processing Options” within Report Manager (accessed via the “Manage” menu option).

     

2.  Cached Reports.  If you have a long-running query that cannot be tuned further, one option you might look into is caching.  With this method, the first user that executes the report will wait for the report to render.  Subsequent users, until the cache expires in minutes or on a schedule, will view the cached report.  Getting the benefit of caching depends on the subsequent users having the same security and same parameter choices that the first user had.

     

3.  Snapshots.  If you have a long-running query, or perhaps a dataset you don’t want users accessing at any & all times of the day, then a snapshot might be a great option.  A snapshot can be thought of as a pre-executed report, usually run on a schedule.  The rest of this blog entry focuses on using snapshots.

     

Prerequisites for Using a Snapshot in Reporting Services

There’s a couple of requirements in order to use a snapshot.  If you are familiar with subscriptions in SSRS, these will sound familiar.

a.  Stored credentials for the data source.  Windows authentication is not acceptable when a report is scheduled.  If you aren’t using a data source with the ID and Password stored (so that data level security is a non-issue when it runs), you’ll get the message “Credentials used to run this report are not stored.” 

     

Tip:  Create a separate shared data source that has a name such as “MCGardenCenterDW_StoredCreds” so you know at a glance it’s using a specific ID and Password as opposed to Windows Authentication (assuming Windows auth is your default).  You may even put these any data sources with stored credentials into their own folder with limited permissions.

b.  Defaults for all Parameters.  Another prerequisite for using snapshots is that all parameters need a default value defined.  This makes sense as there’s no user interaction when a snapshot is being populated at, for instance, 4am.

     

Tip:  Sometimes you might have to be a bit clever in order to ensure all parameters have a default.  For instance, if the default is always “current fiscal period” that continually changes, you can make this happen with an intermediary hidden parameter – it might just take a bit of extra time to think up a good solution.

Scheduling a Snapshot

As you are perusing the options when you “Manage” a report, you will notice there’s two different schedules associated to snapshots.  First, there’s the scheduling options on the “Processing Options” page:

     

Secondly, there’s scheduling options on the “Snapshot Options” page:

     

Initially you might say “huh?” or wonder which should be set.  The key here is that there’s really two kinds of snapshots…

Two Different Types of Snapshots in Reporting Services

On the Processing Options page, you would schedule a “Report Execution” snapshot.  The primary purpose for a Report Execution snapshot is usually to improve performance by reducing report rendering time, or to ensure queries are passed to the source database at very specific times.

Conversely, on the Snapshot Options page, you are setting up a “Report History” snapshot.  The primary purpose for a Report History snapshot is to keep a copy of the report at a specific point in time.

So, basically you want to make sure you set the schedule associated to the purpose you’re trying to accomplish.  Usually it’s just one or the other, but it could be both depending on the requirements.  Microsoft explains it like this:

“Snapshots that are generated as a result of report execution settings have the same characteristics as report history snapshots. The difference is that there is only one report execution snapshot and potentially many report history snapshots. Report history snapshots are accessed from the History page of the report, which stores many instances of a report as it existed at different points in time. In contrast, users access report execution snapshots from folders the same way that they access live reports. In the case of report execution snapshots, no visual cue exists to indicate to users that the report is a snapshot.”

Avoiding the “Selected Report is Not Ready for Viewing” Error

Let’s say on the Processing Options page you checked the radio button to “render this report from a report snapshot.”  Then you scheduled it on the Snapshot Options page.  Sounds reasonable, right?  You can even view the report within the Report History just fine.  However, you click on the report name (i.e., the normal way to run a report) you get a message:  The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available.”

     

Why the message?  And where’s the report?  What happened is with the setup just described, a Report History snapshot now exists but no Report Execution snapshots exists.  So, SSRS has no report to render given this circumstance.  To resolve, it does depend on what you are trying to accomplish, but you probably want to schedule it on the Processing Options page instead.  (Alternatively, if the data rarely changes you could control when it’s created by using the Apply button option.  The Apply button is also useful for generating the first snapshot for testing.)

Viewing Report Execution Snapshots within Report History

There’s one more really important thing to be aware of.  Let’s say you schedule your Report Execution snapshot within the Processing Options.  The report runs fine, but you don’t see it within Report History. 

By default the option to “Store all report snapshots in history” is not checked.  If you want to see a Report Execution snapshot within history, you’ll want to check the box to do so within Snapshot Options.  If you’re only using Report Execution snapshots for this report, you’ll probably want to store them in the history.  However, if you happen to be using both Report Execution and Report History snapshots for the same report, you might want to leave it unchecked so they don’t get mixed up – i.e., if you are doing both, then the schedules will differ for a reason.

     

Letting the User Know When the Data was Refreshed

Since a snapshot will render data at a particular point in time, it’s very kind to the end users of the report to display the “data as of” data in the report header (or footer, however you have it standardized).  To ensure it displays when the snapshot was created, you’ll want to use the ExecutionTime global field (as opposed to Now() or Today() type of functions).  For example:

      =FormatDateTime(Globals!ExecutionTime, DateFormat.ShortDate)

Finding More Information

MSDN – Set Report Processing Properties

MSDN – Processing Options Properties Page (Report Manager)

Connectivity Requirements of Power View in Excel 2013

Overview:  Quick tip about using Power View in Excel 2013 re: how data refreshes are handled, as well as requirements for data connectivity and Internet connectivity.

Power View Refresh Behavior Upon Opening the Excel File

Recently I had an “aha” moment when I realized Power View acts differently than a PivotTable when you open a previously saved Excel 2013 file.  When you open an Excel file that has previously been saved, a PivotTable will render its last saved state (if the external data connection is *not* set to “Refresh data when opening the file”).  I usually explain to new self-service users that this behavior of a PivotTable can be a bit of a security hole if an Excel file is emailed to someone who doesn’t have formal security to see the data – the recipient can see the data last saved in the PivotTable but they couldn’t refresh it unless they have permission to do so (if the external data connection is *not* set to “Refresh data when opening the file”).

However…Power View does not behave the same way.  When you open a previously saved Excel file, it immediately wants to refresh the Power View sheet when you click on it.  As far as I’m aware, Power View cannot render its last saved state, nor can it render a snapshot as of a point in time.

For example, the following screen shot displays a Power View sheet where the external Tabular model data source is unavailable.  The Power View message says “Sorry, something went wrong while loading the model for the item or data source ‘DataSourceName’. Verify that the connection information is correct and that you have permissions to access the data source.”

This behavior tells us we need to have connectivity to our external data source in order to view the Power View report.

Online Connectivity Requirements for using Power View

From what I’ve learned so far, there are the following online requirements for Power View reporting in Excel:

1. Access to External Data Sources (i.e., if accessing an external Tabular model instead of an embedded PowerPivot model).  This might mean a worker has to VPN into their office if using an Excel file while at home, for instance.
2. Internet Connectivity if using Maps (Power View integrates with with Bing Maps).
3. Internet Connectivity if using Image URLs (as opposed to binary images embedded in the data model).

Noticed anything else along these lines?  Please leave me a comment and I’d be happy to add it.

Me? A SQL Server MVP? That’s What They Tell Me!

I am delighted to announce that Microsoft has granted me (me?!?) the MVP Award.  The official description of this award:  “Microsoft Most Valuable Professionals are exceptional technical community leaders worldwide who actively share their high quality real world expertise with others.”  The Microsoft MVP public site is at:  http://mvp.microsoft.com. 

As the week has progressed since the announcement, finding the words to express my feelings about it is becoming harder rather than easier – there are so many people in the community doing great things! Not sure how I made the cut, but I’m looking forward to learning from all the other MVPs and Microsoft folks I’ll be exposed to, and continuing to contribute BI content to the community.

Many times I’ve said the Microsoft user community is very different from others. The extent to which information is freely shared is astounding. We should all greatly appreciate the ability to fire up our search engine and find advice which saves us time and teaches us something. So many people invest time and effort to make that happen.  There are blogs, user groups, books, SQL Saturdays, webinars, Twitter, forums – with many great friendships made along the way.

This week I’ve been thinking a lot about how much our work lives depend on being fearless. What I mean by that – technology changes all the time, business methods vary, best practices evolve, we change, and the people around us change and grow. Many times we find ourselves doing something unfamiliar. The more experience I gain, the less I fear the unknown and the more confidence I have that we as a team can “figure it out.” The sheer volume of information that’s available to us in the Microsoft community to improve our skills makes “figuring it out” faster, easier, and with less risk for poor decisions.

Now I don’t want to pull a Sally Field moment here, but there’s a few people I want to call out. A heartfelt thanks goes to Rafael Salas who submitted an MVP nomination on my behalf.  Wayne Snyder was instrumental in giving me the confidence to start blogging.  My good friends Javier Guillén and Jason Thomas – these guys are always willing to review a draft, exchange ideas, or just go for a beer – they make me smarter by association.  My employer, Intellinet, has been extremely good to me - the level of support and encouragement I’ve received from Leo Furlong and everyone at Intellinet has been amazing.  Finally, my husband Bob is my rock.  That says it all.

Today I received a really nice plaque and certificate.  See the little 2013 disc on the left side of the MVP plaque?  If an MVP award is renewed the following year, the recipient receives another disc to slide onto the plaque.  Cool, huh?

Here’s to a great rest of 2013!

Creating a Power View Report in Excel 2013 Which Uses an External Data Connection

Overview:  Quick tip about how to insert a Power View worksheet in Excel 2013 that points to an external data connection (as opposed to an internal PowerPivot model).  Also includes a tidbit at the end about why the behavior of data connections makes Power View unable to be used for snapshot reporting.

What Doesn’t Work With an External Data Connection

You’ve probably noticed on Excel’s Insert menu a choice for Power View.

    

The Insert menu works beautifully when a PowerPivot model is embedded within the same Excel workbook that is open.  However, if an embedded PowerPivot model is not available, instead you get a message:  “Power View needs data to work with.”

    

So, let’s investigate a different technique in order to make an external connection to an Analysis Services Tabular model.

Connecting to an External Data Source

If your connection already exists, choose it in Existing Connections and skip to Step 4.

1.  On the Data menu, choose From Other Sources > From Analysis Services.

    

2.  Select the Tabular Model you want, then Next.

    

3.  Modify the File Name and Friendly Name if you’d like, then Finish.

    

4.  Change the radio button to Power View Report (instead of the default PivotTable Report).

    

You probably also want to visit the Properties pane.  By default, the checkbox to “Refresh data when opening the file” is not selected. 

    

If you leave the “Refresh data when opening the file” option unchecked (which is the default), when the file is reopened Power View will render a message “Please refresh to see the data for this Power View sheet.” Doing a Refresh fixes it, but it’s one extra click every single time you open the file.

    

Inherently what this means is that Power View cannot be used as a tool for snapshot reporting.  With an Excel PivotTable, unless you choose to refresh the data you see the last saved state.  Power View, however, does not behave the same way – it won’t render the last saved state.  It must be refreshed.  Therefore, this means to save a snapshot at a point in time the report would have to be exported.

To change the setting after it’s been set up, go to Connections on the Data menu, then choose Properties.