Archive

Author Archive

Spreadsheet Audit and Control in SharePoint 2013 Preview

August 22, 2012 Leave a comment

One of the Great New Things (TM) in SharePoint 2013 is the technology Microsoft acquired from Prodiance. This allows organisations to discover and audit the spreadsheets within their organisation. The spreadsheets could be tracked based on number of attributes (some of the more relevant to BI are data connections, conditional formatting, data changes in Pivot tables, file size). Once a set of critical spreadsheets are identified, users and IT administrators can compare spreadsheets
individually (pair-wise) for data consistency, formula errors etc. with Inquire in Excel. You can find out more here: http://blogs.office.com/b/microsoft-excel/archive/2012/07/26/office-business-intelligence-the-way-people-experience-data.aspx

However if, like me, you want to get the server bits for this and evaluate them, it can be quite tricky to actually work out what to download. Well, this is what you need:

http://www.microsoft.com/en-us/download/details.aspx?id=30353

 

Advertisements

Shrinking a fixed-size VHD file

June 18, 2012 Leave a comment

In playing around with the new Azure Virtual Machines I wanted to upload an existing demo VM. It was a dynamically expanding VHD capped at 250Gb, but Azure only accepts fixed-size disks, and I certainly didn’t want to upload anything larger than was necessary. So to convert and shrink the drive I had to go through a slightly convoluted process:

1. Compact the file through Hyper-V, and then Convert it to a Fixed size (use the Inspect Disk option in the right-hand pane of Hyper-V management console)

image

2. Boot into a Win PE environment in the VM, so you can get to the DiskPart utility. You need to select the relevant volume (SELECT VOLUME x), then run SHRINK to take the volume down to the size required. Bear in mind you probably want some extra overhead for growth again! After this you can turn the VM off again.

3. Download VHDResizer from http://vmtoolkit.com/files/folders/converters/entry87.aspx, this allows you to resize the VHD:

image

Categories: SQL / BI

Microsoft Project Barcelona

October 20, 2011 Leave a comment

One of the internal projects I’ve been following for some time has gone public and are now blogging about their development process. It’s a really interesting read from a dev point of view, but the product itself is also very clever. It’s addressing the ‘impact analysis’ and ‘data lineage’ world.

Imagine you’re a DBA, and you want to make a change to a column in a table. What views are connected to that table? What ETL packages refer to that view? What data source does that package load? Are there cubes built from that warehouse? Are there reports using the cube? Are their Excel spreadsheets connected to that report?

This chain of data is complex and for most organisations ever-changing. Project Barcelona aims to address this by crawling all these objects and building a node graph showing the movement of data throughout. They explain it much better than I can: take a look at their blog here: http://blogs.msdn.com/b/project_barcelona_team_blog/

I love that they’re publishing previews of the user experience online through Azure as well, at http://projectbarcelona.cloudapp.net/:

barcelona

Cool stuff.

Categories: SQL / BI

Combining SSAS’s DefaultMember and Roles

June 16, 2011 Leave a comment

I’ve been asked if a cube could be exposed to users so everyone gets ‘their’ view of data by default, but is able to compare with others if they wish. a combination of setting the DefaultMember on a per-role basis seems to be the answer. Take an AdventureWorks example: if I’m a sales manager responsible for the UK, most of the time I only want to report on that. But, come annual review time I probably want to see how I compared to my peers. If there’s a ‘UK Manager’ role set up in the cube, to which I belong, when I build a pivot table I see just values for the UK by default:

image

But, if I choose to include Countries in the query, everything shows up:

image

To set up the role, choose the appropriate MDX statement in BIDS:

image

Make sure you’ve got the corresponding Attribute chosen in the drop-down and specified in the MDX (Country in this example).

Categories: SQL / BI

Bubble-up Exceptions in SSAS KPIs

June 16, 2011 Leave a comment

A requirement to see, at a summarized level, how many exceptions appear at lower points in a hierarchy is a pretty common OLAP scenario, known as ‘bubble-up exceptions’. ProClarity’s KPI Selector did a great job of defining these, but when it’s not available, you can define them as a SSAS KPI. An example from AdventureWorks showing how many products there are with below 37% GPM (paste into the Value Expression in BIDS):

IIF
(
    Count
    (
      Filter
      (
        Descendants
        (
          [Product].[Product Categories].CurrentMember,
          [Product].[Product Categories].[Product]
        ),
        
            (
              [Measures].[Internet Gross Profit Margin],
              [Product].[Product Categories].CurrentMember
            )
          > 0
        AND 
            (
              [Measures].[Internet Gross Profit Margin],
              [Product].[Product Categories].CurrentMember
            )
          < 0.37
      )
    )
  > 0,
  Count
  (
    Filter
    (
      Descendants
      (
        [Product].[Product Categories].CurrentMember,
        [Product].[Product Categories].[Product]
      ),
      
          (
            [Measures].[Internet Gross Profit Margin],
            [Product].[Product Categories].CurrentMember
          )
        > 0
      AND 
          (
            [Measures].[Internet Gross Profit Margin],
            [Product].[Product Categories].CurrentMember
          )
        < 0.37
    )
  ),
  NULL
)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Here’s your result:

image

Categories: Uncategorized

BI Sessions at TechEd/BI Conference

June 24, 2010 Leave a comment

A couple of weeks ago Microsoft hosted TechEd 2010, which included the BI Conference. Over 55 hours of content has been made available, and Kevin Lief, one of the chaps responsible for BI channel development in the UK, has collated a great document with links to all the most relevant BI sessions. I’ve shared it on my SkyDrive here. One particularly good session is from Donald Farmer, giving a 101 overview of what BI is and why it’s of use.

Categories: SQL / BI

When graphs go wrong!

As always, Stephen Few has picked up on a fantastic mis-representation of the facts through data visualisation, in this blog post it’s levelled at BP and their efforts to plug up the oil slick disaster. Misleading graph is misleading!

Categories: SQL / BI