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.

Comparison of Master Data Services Functionality in Web Interface versus Excel Add-In

There are some differences in functionality available in the MDS web interface (known as Master Data Manager) versus the MDS Excel Add-In.  Following is a quick reference which is applicable to SQL Server 2012.

Management of Data:

Feature	Web Interface	Excel Add-In
Add or update members individually	Yes	Yes
Delete members individually	Yes	Yes
Add or update members in bulk	No	Yes
Delete members in bulk	Yes	Yes
Create annotations of changes	Yes	Yes
Create and manage attribute groups	Yes	No
View attribute groups	Yes	Yes (Via filters)
View transactions	Yes	No
Filters	Yes	Yes (Fewer options)
Match data	No	Yes (If DQS integration is enabled)
Combine data	No	Yes
Create shortcut query file	No	Yes

Model Structure:

Feature	Web Interface	Excel Add-In
Create model	Yes	No
Create entity	Yes	Yes (Code and Name columns only)
Create attribute	Yes	No
Edit attribute data type or length	No	Yes

Business Rules:

Feature	Web Interface	Excel Add-In
Create and edit business rules	Yes	No
Apply business rules (validate)	Yes	Yes

Hierarchies:

Feature	Web Interface	Excel Add-In
Create explicit hierarchy	Yes	No
View explicit hierarchy	Yes	Yes (Via filters)
Manage explicit hierarchy	Yes	No
Create derived hierarchy	Yes	No
View derived hierarchy	Yes	No

Collections:

Feature	Web Interface	Excel Add-In
Create and view collections	Yes	No
Add or update collection members	Yes	No

Administration:

Feature	Web Interface	Excel Add-In
Administrator tasks such as managing permissions, versions, subscription views, and deployment	Yes	No

New and Discontinued MDS Features in SQL Server 2012

Recently I did some research to become more familiar with the new features of Master Data Services in SQL Server 2012, as well as what’s discontinued and deprecated.  Below are my notes compiled from reading a variety of sources.

New MDS 2012 Features

New Excel Add-In

• A new Excel Add-In is introduced, which provides an optional alternative to working in the Web Interface.  The Add-In can be downloaded here:  http://www.microsoft.com/en-gb/download/details.aspx?id=35581
• Works on Excel 2007 forward.  (Exception:  To use the “Send Query” functionality on the Master Data ribbon, this requires Outlook 2010.)

              

New Shortcut Query Files

• After you have loaded MDS data into an Excel worksheet, you can use the “Save As Query” menu item on the Master Data ribbon.  This will save a Shortcut Query File to be reused.  There’s also a “Manage Queries” option to load, rename, or delete previously saved queries.
• Shortcut Query Files have the following advantages:  (1) Saves time and improves accuracy when reloading data for a particular set of data on a regular basis, especially if you've placed filters on the set data.  (2)  Allows you to share the query with a coworker without having to email the data itself in the workbook – this is particularly helpful if it’s sensitive data.  (3) By posting the Shortcut Query File to a SharePoint Document Library, data stewards may utilize it as a starting point within their own Excel workbook.  This improves accuracy, consistency, and efficiency.
• File format of a Shortcut Query File is *.mdsqx XML format which can be imported and exported for ease in sharing between coworkers.  If you execute the *.mdsqx file, it uses an MDSQueryOpener program (which opens Excel, runs the query, and renders the data).

              

Redesign of the Web Interface

• Master Data Manager now uses Silverlight 5.
• Advantages of the redesigned interface include:  (1) It is faster.  (2) Less page refreshes are required.  (3) Adding, deleting & moving records is quicker.  (4) The layouts are more consistent from screen to screen.  
• Additional filtering capabilities have been added to the Web Interface (note: the Web UI offers a few more filtering capabilities than the Excel Add-In).

              

New Integration with Data Quality Services

• Purpose of the integration with DQS is to ensure no duplicate records exist.  Integration is currently limited to this particular function.
• This functionality works only in the Excel Add-In.  You can use the “Match Data” button on the Master Data ribbon (or if you set up a staging ETL process using SSIS).  It’s not supported in the Web Interface with this release.
• Three requirements to use the DQS matching functionality:  (1) The integration with DQS needs to be enabled in the MDS Configuration Manager – the Data Quality section of the Master Data ribbon is not visible until enabled.  (2) A matching policy needs to exist within the DQS Knowledgebase.  (3) Both MDS and DQS_MAIN need to exist on the same SQL Server instance.

              

Restructured Entity-Based Staging Structure

• Rather than using shared staging tables for use with ETL processing, each entity now has its own group of staging tables including stg.name_leaf, stg.name_consolidated, and stg.name_relationship.
• Advantages of having separate staging tables per entity:  (1) The ETL runs more efficiently.  (2) Security can be set up per individual table.  (3) Members and attributes may be loaded in single batches.
• The stored procedures which populate your model from the staging tables support inserts, updates, and deletes.  This is a HUGE improvement in the 2012 version, considering the 2008 R2 staging tables could not handle updates to existing attributes.

              

Simplified Security Model

• Derived hierarchies will inherit its permissions from the model.  Security for a derived hierarchy can no longer be set explicitly.
• Explicit hierarchies will inherit its permissions from the entity.  Security for an explicit hierarchy can no longer be set explicitly.
• Attribute Groups now have a new Attribute Group Maintenance page to assign update permissions.  Read-only permissions can no longer be set.

              

New Deployment Command-Line Tool

• A new command-line tool called “MDSModelDeploy” is introduced.  This allows deployment of not only the model structure, but the data as well.  (Note:  the Web Interface only supports deployment of the structure, not the data.)
• After deployment, the following items must be manually updated:  (1) User-defined metadata, (2) File attributes,  (3) User and Group permissions.

              

New Option to Create Code Values Automatically

• A new option exists to have Code Values created automatically when a new member is added to an entity. In the previous version of MDS, this could be accomplished via a business rule.  However, this new option under Manage>Entities is much more convenient because it takes effect as soon as the member is created.

              

Miscellaneous

• Collections now may have a weight assigned to each item.
• If you add the MDS Web Interface to a page with a SharePoint portal, you can add “&hosted=true” as a query parameter to the URL string.  This reduces the space required to display Master Data Manager.
• Installation of Master Data Services is now part of SQL Server.
• Master Data Services is available in the Business Intelligence or Enterprise editions.

Discontinued MDS 2012 Features

• One side-effect of the Web Interface improvements (see the Redesign of Web Interface section above) is that batch updates are no longer supported.  Of course, doing updates one by one is not efficient if you have a large number of members to update.  Your alternatives for handling a large number of updates is the Excel Add-In or a staging ETL process.
• The new staging process does not support maintenance of Collections, nor does the Excel Add-In.  Collections may only be maintained via the Web Interface.
• Business Rules to generate Code Values are no longer supported.  Alternatively, you will want to use the new feature when the entity is set up (see the New Option to Create Code Values Automatically section above).
• The ability to explicitly secure hierarchies and attribute groups has been eliminated.  (See the Simplified Security Model section above.)
• Attribute Groups can no longer have read-only permissions (update permissions only).
• The Web Interface no longer displays an “Export to Excel” button.  Instead of pushing the data from MDS to Excel, you may use the Excel Add-In to pull the data from MDS to Excel.
• Users (i.e., a typical data steward) no longer have the ability to reverse their own transactions.  Reversal of an MDS transaction now requires an administrator.
• Annotations cannot be deleted.  Rather, annotations are now retained permanently.
• Powershell cmdlets are no longer available in this release.

Deprecated MDS 2012 Features

• The consolidated staging table approach as used with SQL Server 2008 R2 will be removed from a future version.  This includes tblStgMember, tblStgMemberAttribute, and tblStgRelationship plus the udpStagingSweep stored procedure.  If you go with Upgrade Choice #2 (see Upgrade Considerations below), you can still use the 2008 R2 staging processes at this point in time.  However, they will be likely removed in a future release.
• The Metadata model will likely be removed in a future release.  Although you can see it, it shouldn’t be used for anything.

Upgrade Considerations

When you are ready to upgrade MDS from SQL Server 2008 R2 to SQL Server 2012, you need to make a decision if you are ready to upgrade the database engine or not.  There are two approaches:

1. Upgrade the Web Application and the MDS database to SQL Server 2012.
2. Upgrade the Web Application, but leave the MDS database at SQL Server 2008 R2.  With this choice, the database schema is updated to support new features, and you can use the new Web Interface.  You may continue using the 2008 R2 processes (such as the consolidated staging format, or the management of Collections via the staging table structure) as-is until you are ready to redesign them.  A downside to this approach is that you don’t see all staging processes displayed within the Web Interface.  It does buy you some time to redesign though, which is helpful if you have a lot of MDS SSIS packages.

Finding More Information

MSDN – SQL Server Master Data Services

TechNet – What’s New in Master Data Services in SQL Server 2012 RC0

MSDN – Upgrade Master Data Services

MSDN – Discontinued Master Data Services Features in SQL Server 2012

Jeremy Kashel’s Blog – Master Data Services SQL Server 2012 Vs 2008 R2

Ross Mistry and Stacia Misner - Introducing Microsoft SQL Server 2012

New Auditing and Compliance Features for Excel and Access

Office 2013 introduces some great new features to help audit and manage Excel workbooks and Access databases – sometimes referred to as EUCs or End User Computing applications. 

Although I’m a huge proponent of enabling knowledge workers with Self-Service BI capabilities, I’m equally a proponent of IT assuming responsibility for managing and securing the environments.  These features, an evolution from the Prodiance Corp. acquisition, are a welcome addition for both end users and IT to manage Excel and Access.  And as a bonus, your auditors will love it!

Office 2013 desktop desktop applications   <—Focus:  1-2 files.  Target audience: Anyone with Excel

• Inquire
• Spreadsheet Compare
• Database Compare

Office 2013 server applications    <—Focus:  many files.  Target audience: IT system admins, auditors, or anyone who wants to monitor changes to a set of files

• Microsoft Office Audit and Control Management Server 2013
• Microsoft Discovery and Risk Assessment

Following is a brief review of the capabilities of each item.

---

Inquire

Applicable to:  Excel 2013 (Office Professional Plus)

Accessed via:  Inquire Ribbon Menu inside of Excel

   

Capabilities:

• Workbook Analysis – Report which contains metadata about a workbook such as formulas, cells, ranges, data connections, and links.  Useful for documentation and to aid in understanding a workbook.
• Workbook Relationship – Diagram which maps out the links to other workbooks & data sources.  Helpful to analyze dependencies, lineage, and the potential effect of changes.
• Cell Relationship – Diagram which maps out links and formulas between cells within the same workbook or other workbooks.
• Compare Files – Compares two workbooks currently open; highlights the differences cell by cell. The differences are color-coded and categorized so more critical changes, such as a change to a entered values or calculated values, is separate from an immaterial change like a change to a cell format.  This is actually the same functionality as the Spreadsheet Compare tool, discussed in the next section; this feature is just available within Excel as a convenience.
• Clean Excess Cell Formatting – Eliminates unneeded formatting to reduce the size of the file and/or improve performance.
• Workbook Passwords – A Password Manager which stores passwords for Inquire so it can open and perform analysis on password-protected workbook(s).

More info: What You Can Do With Spreadsheet Inquire

---

Spreadsheet Compare

Applicable to: Excel 2013 (Office Professional Plus)

Accessed via:  Start > All Programs > Microsoft Office 2013 > Office 2013 Tools > Spreadsheet Compare 2013

Capabilities:  Compares two workbooks currently open; highlights the differences cell by cell.  The differences are color-coded and categorized so more critical changes, such as a change to a entered values or calculated values, is separate from an immaterial change like a change to a cell format.

More info:  Basic Tasks in Spreadsheet Compare

---

Database Compare

Applicable to: Access 2013 (Office Professional Plus)

Accessed via:  Start > All Programs > Microsoft Office 2013 > Office 2013 Tools > Database Compare 2013

Capabilities:  Compares two Access databases and highlights the differences in a query, form, report, or code.  What it will not do is compare the difference in data stored within the tables (workaround: export the data to excel and use Spreadsheet Compare).

More info:  Basic Tasks in Database Compare

---

Microsoft Office Audit and Control Management Server 2013 (ACM 2013)

Capabilities:

• Logs and reports on changes made to critical spreadsheets.
• Cell level auditing, versioning, and audit trails.
• Change control and data integrity.
• Segregation of duties.

---

Microsoft Discovery and Risk Assessment

This is actually part of the ACM 2013 suite of products (discussed in the previous section).  This product was formerly known as Prodiance eDiscovery.

Capabilities: 

• Creates an inventory of Excel workbooks and Access databases within the file system and/or SharePoint document libraries.
• Rates the level of complexity, materiality/impact, and risk to the organization.
• Identifies errors in formulas.
• Identifies broken links.
• Creation of custom rules for compliance purposes.

More info: Use Microsoft Discovery and Risk Assessment

---

Finding More Information

Excel Blog – Introducing spreadsheet controls in Office 2013!

Access Blog – Feral Cats:  Managing Access databases in your organization

Viewing SSAS Member Properties within an an MDX Query Window in SSMS

Overview:  At a user group meeting recently, I was asked by someone how to view Member Properties (Dimension Properties) when running an MDX query in SQL Server Management Studio.  He could see them in Excel, but hadn’t discovered where to view them in SSMS.  And with that, inspiration for this blog entry was born.

Level:  Assumes a basic familiarity with SSAS attributes, MDX queries, & attribute relationships.

And Just What are Member Properties?

As stated in BOL, Member Properties “cover the basic information about each member in each tuple.”  There are two types of Member Properties:

• Intrinsic Member Properties.  These are your standard properties such as UniqueName, Caption, Level, etc.
• User-Defined Member Properties.  These are the attributes available via attribute relationships.

Querying Intrinsic Member Properties in Management Studio

Let’s start with a really straightforward query – no Dimension Properties are being requested in this first example query.  Yet, when we double-click on the Australia cell, we do indeed see the standard Intrinsic Member Properties – without even needing to ask for them in the query syntax.

    

Just for comparison sake, in the following example we have added some of the standard Intrinsic Member Properties to the query syntax.  Note that these are retrieved using syntax of “DIMENSION PROPERTIES” within the “ROWS” section.  Now if we double-click on the Australia cell again, we see the 3 Member Properties we requested (duplicates of the standard ones above, just to keep things simple).

    

Querying User-Defined Member Properties in Management Studio

In this 3rd example things get interesting.  Sales Territory Group is related to Sales Territory Country via the SSAS attribute relationships. 

    

Due to this relationship, we can choose to retrieve Sales Territory Group via the “DIMENSION PROPERTIES” syntax if we desire.  Note that in the following query, Sales Territory Group does not come back within the “visible” query results.  Rather, it’s being returned as a Member Property which can be seen with a double click:

   

Here’s how that attribute relationship looks when browsing the metadata in SSMS.  Note that Sales Territory Country is shown under the Member Properties folder:

    

In the above example, the “Sales Territory Group” is indeed a visible attribute.  However, if Sales Territory Group weren’t visible for browsing (yet still enabled), one way to access it would be via the “DIMENSION PROPERTIES” syntax shown above.  (Another way would be through a calculated member – see example here.) 

Viewing User-Defined Member Properties in Excel

Let’s put an additional frame of reference around our previous Sales Territory Group example by comparing what we saw in SSMS to what we’d see in Excel.  Let’s say we’re browsing Excel as follows:

    

…and we right-click on the Australia cell to see that Sales Territory Group is visible under the “Show Properties in Report” menu:

     

Finding More Information

MSDN – Using Member Properties

Comparing Slicers in Excel 2010 to Standard PivotTable Filters

Overview:  This blog entry discusses using slicers within Excel 2010 to filter the data being presented, with an emphasis on choosing between traditional filters & slicers. 

Level:  Basic understanding of an Excel PivotTable is helpful.

Slicing and Dicing a Set of Data

The phrase “slice and dice” is used a lot when referring to the analysis of data.  The Slicing part refers to filtering your data to focus on just a subset – essentially the “where” statement.  Dicing the data refers to selecting which attributes we’re grouping the data by.  For instance, if we have a PivotTable showing Internet Sales for 2006 and 2007 by Country and by State, then we have sliced the data by year and diced it by geography.

What is a Slicer in Excel 2010?

Excel 2010 introduces slicers as an alternative to traditional PivotTable filters (i.e., the Report Filter and the Row/Column Filters). 

Following is an example of how slicers are presented in Excel:

                                  The above screen shot uses publicly available data I originally downloaded from the UK Guardian Data Store.

If you’re not familiar with slicers, take a quick look at the following before reading on: 

• Easy (And Even Fun!) Data Exploration:  Introducing Excel 2010 Slicers, and
• Interacting with Slicers

Connecting Slicers to One or More PivotTables

Connecting a slicer to more than one Table is the greatest benefit of slicers & what makes them better than traditional PivotTable filters (i.e., the Report Filter and the Row/Column Filters).  Slicers are extremely useful if you create a dashboard page and you want one click to affect multiple items on the page.  There’s a catch though:  the multiple PivotTables must use the same underlying data connection. 

---> Hint! Using PowerPivot to mashup, or consolidate, your data from different sources can be of real benefit.

      

If you are displaying (and slicing on) a PivotChart, there must be a PivotTable “underneath” the PivotChart.  In the Book Sales Dashboard screen shot shown above, I placed the PivotTables which support the two charts on a hidden worksheet.

Choosing Between Slicers & Traditional PivotTable Filters

Technically, this is not an either/or decision.  Slicers can be used in conjunction with standard PivotTable filter functionality (i.e., the Report Filter, Column, and Row Filters). 

A Slicer may work better if:

• You want to control filtering of > 1 PivotTable with one click.  Since traditional filters are associated with one PivotTable only, slicers really have the potential to make Excel dashboard far more functional.
• You want to display the same field in the slicer as well as on the columns or rows.  The ability to slice & dice by the same field is not allowed by the Report Filter.
• Seeing the items currently selected directly on the worksheet is helpful (i.e., the current state of the filter), as opposed to “Multiple Items” in the Report Filter, or just a small icon which can easily be overlooked.
• Flexibility for placement, sizing, or formatting of the slicer is of importance.
• You only have 2-4 different fields to filter on (more than that can just take up so much space).

      

A traditional Report Filter may be better if:

• Space on the page is at a premium. Whereas slicers take up quite a bit of space, Report Filters show their selections via a compact drop-down menu.
• Your user base is familiar with standard PivotTable functionality.
• You have more than 4 or 5 different fields to filter on.
• The user interaction needs to be a hierarchical tree (ex: Country > State > City).  A Report Filter will show these in a tree structure, whereas a slicer will split them up into separate (but related) slicers.

        

A traditional Row/Column Filter may be the best choice if:

• You need Top 10, Label Filters, or Value Filters which are part of traditional PivotTable filters.  (Slicers and Report Filters work filter on attributes only.)
• Space on the page is at a premium. Whereas slicers take up quite a bit of space, Row/Column filters are embedded within the PivotTable itself.
• Your user base is familiar with standard PivotTable functionality.
• You have more than 4 or 5 different fields to filter on.
• The user interaction needs to be a hierarchical tree (ex: Country > State > City). A Row/Column Filter will show these in a tree structure, whereas a slicer will split them up into separate (but related) slicers.

      

Slicers in the PowerPivot Field List

A PivotTable that is associated to a PowerPivot data source has two additional Slicers panes available that the standard Excel PivotTable doesn’t have.

The “Slicers Vertical” pane in the PowerPivot Field List:

    

The “Slicers Horizontal” pane in the PowerPivot Field List:

    

When you add a slicer to the PowerPivot Field List (either the Vertical or Horizontal shown above), Excel automatically creates a rectangle outside of the slicer.  If you move the slicer around on the workbook, outside of the rectangle, the slicer will still work – however, it disappears from being shown in either of the slicer panes.  Basically it becomes a stand-alone slicer in this situation.

A stand-alone slicer used with a standard PivotTable Field List (i.e., not associated to PowerPivot):

     

Finding More Information

The Basics:

Excel 2010 Help – Use Slicers to Filter PivotTable Data

Excel Blog - Easy (And Even Fun!) Data Exploration: Introducing Excel 2010 Slicers

Excel Blog – Interacting with Slicers

More Advanced:

MSDN – How to Highlight Table Rows Based on Slicer Selection (a great technique if you’re ready for something a bit more advanced)

Javier Guillen’s Blog – PowerPivot Slicers

Jen Stirrup’s Blog – Excel Slicers and Good Interface Design