Search
Twitter

Entries in Excel (9)

Thursday
Dec192013

Getting New Power Pivot Features & Other Office 2013 Updates

New Power Pivot Features Released

Have you heard that Power Pivot just got a new Synonyms feature?  Synonyms associate column (attribute) names in a data model with alternate terms so that the Q&A Natural Language Query functionality in Power BI will be able to return results when users search for data using various terminology. Metadata like this will continue to to be more and more important in terms of making the user experience better, but metadata actually isn't what I want to write about today…today I want to focus on how to get new features like the Synonyms in Power Pivot as quickly as possible.

Starting with Excel 2013, Power Pivot is integrated into Excel’s data model. That makes Power Pivot currently dissimilar to Power Query which requires updates to be downloaded and installed. Power Pivot updates will come along with other Office 2013 updates that are released.

Just how do you get Office 2013 updates anyway?

How you get updates depends on how you installed Office 2013. There are two ways to install Office 2013:

  • The Click-to-Run type of installation, or
  • The traditional MSI Windows-based installer

I was inspired to investigate further because, when I checked for the new Synonyms feature in my version of Power Pivot, I didn’t have it yet.  This is because I currently have an MSI-based installation rather than Click-to-Run.

Most installations these days are Click-to-Run by default.  The MSI download media is typically only available to Microsoft customers who have a volume license agreement for Office 2013 or MSDN subscribers.

What is Click-To-Run?

Click-to-Run is a mechanism to install and update Office 2013 software products. It has been around a very long time, but has evolved to be much more sophisticated these days.

Like a traditional software installation, Click-to-Run still installs the Office programs on your local machine and uses local machine resources. However, the installation process is done via streaming which is broken up into small chunks to take it easy on bandwidth.  Even if you use Office 365 or Office on Demand sometimes, if you download the software package it does install and run from your machine.

By default, if you installed Office 2013 using the Click-to-Run method, the machine will receive automatic updates for Office 2013 (such as the update to Power Pivot mentioned at the very beginning of this blog entry). It handles installing the updates in the background during a time when the user isn't actively using components which need to be updated.  Although, depending on the kind of update, it’s possible the user will see a notification or a request for the app to be closed so it can proceed.

Interestingly, Click-to-Run is considered to be virtualized which means that all the Office 2013 program files are isolated.  This isolation allows programs to co-exist (such as Office 2010 and Office 2013 side by side).  The virtualization also allows it to continue to receive updates independently of Windows updates.  The streaming and virtualization behavior of Click-to-Run is based on Microsoft Application Virtualization (App-V) technologies.

The biggest benefit of Click-to-Run is that the updates are pushed automatically which means you don't need to install updates, service packs, or patches.  Users will probably love this – system administrators, well, maybe not all of them will love it. Click-to-Run also touts the benefit of being able to start using the software before it's finished installing.  It can do this because it sets up the most commonly used features first and continues to finish in the background.  These commonly used features are known as the “First Run Experience.” 

How to Check What Office Version You Have and if It's Click-To-Run or Not

To check the version that is installed:

  1. Launch Excel ( or any other Office app) and create a new workbook
  2. Choose File > Account

This is what it looks like if you do *not* have Click-to-Run:

      image

If you do have Click-to-Run, you will also see this option on the Account page:

      image

If automatic updates are enabled in the Update Options, then you should be getting the latest Office 2013 updates as they are released.

What if You Don't Have the Updates You Want?

If you’re in a corporate environment, chances are you won’t have much control over this as IT policies will dictate how Office is installed and updated.  System administrators are likely to handle installations using the Office Deployment Tool (ODT). 

If you do have the Click-to-Run version of Office 2013, make sure that updates are turned on (File > Account > Office Updates section).

If you have the MSI version of Office 2013, you have two choices:  One is to wait until the updates come through via the Windows update process (if it doesn’t conflict with your IT corporate policies, you can allow Windows updates to install automatically under Control Panel > Windows Update > Change Settings).  I don't currently know what the time difference is between Click-to-Run feature releases and the Windows Update releases - I'd love to hear more details in the comments if you know.  The other choice is to uninstall Office 2013 and reinstall it using a Click-to-Run version.  From what I’ve read, I believe you are able to keep your same product key.

Before you choose to reinstall Office 2013 with the Click-to-Run method, beware of the following limitations:

  • Not all add-ins and apps will behave the same way
  • Some functionality isn't available (such as SharePoint BCS or Edit in Datasheet functionality)

The full list of known issues can be found here:  http://office.microsoft.com/en-us/excel-help/click-to-run-known-issues-HA101850565.aspx.  On the positive side, search functionality in Outlook is more full-featured with the Click-to-Run.

There are multiple choices for where to download the Click-to-Run software from depending on how it was purchased.  You probably want to start with “My Office” found here:  https://office.microsoft.com/en-us/MyAccount.aspx or this link:  www.office.com/downloadoffice

Another way to download the Click-to-Run version of Office 2013 would be to log onto your Office 365 account.  Click the gear symbol at the top right, then Office 365 settings, then Software.  If you have the type of O365 account that provides the Office suite, then you’ll see the software listed there.  Additional FAQs can be found here:  https://downloadoffice.getmicrosoftkey.com/Home/SupportFAQ.

 

Sunday
Apr072013

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.”

image

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.

 

Saturday
Feb022013

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

 

Friday
Jan252013

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.)

               image

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).

               image

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).

               image

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.

               image

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.

               image

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.

               image

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.

               image

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

 

Monday
Dec172012

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

    image

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

 

Sunday
Dec042011

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.

     image

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).

     image

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. 

     image

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:

    image

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:

     image

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:

     image

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

      image

 

Finding More Information

MSDN – Using Member Properties