Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Archive for the ‘Excel’ Category

Rendering Images In An Excel Worksheet With Power Query Using Cells As Pixels

with 4 comments

It’s a general rule of the internet that, whenever you have a cool idea, a few minutes spent on your favourite search engine reveals that someone else has had the idea before you. In my case, when I first saw the functionality in Power Query for working with binary files I wondered whether it was possible to read the contents of a file containing an image and render each pixel as a cell in a worksheet – and of course, it has already been done and done better than I could ever manage. However, it hasn’t been done in Power Query… until now.

First of all, I have to acknowledge the help of Matt Masson whose blog post on working with binary data in Power Query provided a number of useful examples. I also found this article on the bmp file format invaluable.

Second, what I’ve done only works with monochrome bmp files. I could have spent a few more hours coming up with the code to work with other file types but, frankly, I’m too lazy. I have to do real work too, you know.

So let’s see how this works. Here’s a picture of Fountains Abbey that I took on my phone while on holiday last summer:

FountainsAbbey

I opened it in Paint and saved it as a monochrome bmp file:

FountainsAbbey

Here’s the code for the Power Query query that opens the bmp file and renders the contents in Excel:

let
   //The picture to load
   SourceFilePath="C:\Users\Chris\Pictures\FountainsAbbey.bmp",
   //Or get the path from the output of a query called FileName
   //SourceFilePath=FileName,
   //Load the picture
   SourceFile=File.Contents(SourceFilePath),

   //First divide the file contents into two chunks:
   //the header of the file, always 62 bytes
   //and the rest, which contains the pixels

   //Define the format as a record
   OverallFormat=BinaryFormat.Record([
    Header = BinaryFormat.Binary(62),
    Pixels = BinaryFormat.Binary()
    ]),
   //Load the data into that format
   Overall = OverallFormat(SourceFile),
   //Get the header data
   HeaderData = Overall[Header],

   //Extract the total file size and
   //width and height of the image
   HeaderFormat = BinaryFormat.Record([
    Junk1 = BinaryFormat.Binary(2),
    FileSize = BinaryFormat.ByteOrder(
     BinaryFormat.SignedInteger32,
     ByteOrder.LittleEndian),
    Junk2 = BinaryFormat.Binary(12),
    Width = BinaryFormat.ByteOrder(
     BinaryFormat.SignedInteger32,
     ByteOrder.LittleEndian),
    Height = BinaryFormat.ByteOrder(
     BinaryFormat.SignedInteger32,
     ByteOrder.LittleEndian),
    Junk3 = BinaryFormat.Binary()
    ]),
   HeaderValues = HeaderFormat(HeaderData),
   FileSize = HeaderValues[FileSize],
   ImageWidth = HeaderValues[Width],
   ImageHeight = HeaderValues[Height],
   
   //Each pixel is represented as a bit
   //And each line is made up of groups of four bytes
   BytesPerLine = Number.RoundUp(ImageWidth/32)*4,
   //Read the pixel data into a list
   PixelListFormat = BinaryFormat.List(
    BinaryFormat.ByteOrder(
     BinaryFormat.Binary(BytesPerLine),
     ByteOrder.LittleEndian)),
   PixelList = PixelListFormat(Overall[Pixels]),
   //Convert each byte to a number
   PixelListNumbers = List.Transform(PixelList, each Binary.ToList(_)),

   //A function to convert a number into binary
   //and return a list containing the bits
   GetBinaryNumber = (ValueToConvert as number) as list =>
    let
     BitList = List.Generate(
      ()=>[Counter=1, Value=ValueToConvert], 
      each [Counter]<9, 
      each [Counter=[Counter]+1, 
      Value=Number.IntegerDivide([Value],2)], 
      each Number.Mod([Value],2)),
     BitListReversed = List.Reverse(BitList)
    in
     BitListReversed,

   //A function to get all the bits for a single line
   //in the image
   GetAllBitsOnLine = (NumberList as list) => 
    List.FirstN(
     List.Combine(
      List.Transform(NumberList, each GetBinaryNumber(_)
     )
    ), ImageWidth),

   //Reverse the list - the file contains the pixels
   //from the bottom up
   PixelBits = List.Reverse(
    List.Transform(PixelListNumbers, 
    each GetAllBitsOnLine(_))),

   //Output all the pixels in a table
   OutputTable = #table(null, PixelBits)
in
    OutputTable

 

The output of this query is a table containing ones and zeroes and this must be loaded to the worksheet. The final thing to do is to make the table look like a photo by:

  • Hiding the column headers on the table
  • Using the ‘None’ table style so that there is no formatting on the table itself
  • Hiding the values in the table by using the ;;; format (see here for more details)
  • Zooming out as far as you can on the worksheet
  • Resizing the row heights and column widths so the image doesn’t look too squashed
  • Using Excel conditional formatting to make the cells containing 0 black and the cells containing 1 white:image

 

Here’s the photo rendered as cells in the workbook:

image

And here it is again, zoomed in a bit so you can see the individual cells a bit better:

image

You can download the workbook (which I’ve modified so you can enter the filename of your bmp file in a cell in the worksheet, so you don’t have to edit the query – but you will have to turn Fast Combine on as a result) from here. Have fun!

Written by Chris Webb

January 20, 2015 at 5:11 pm

Posted in Excel, Power Query

Power Pivot / Power Query Read-Only Connection Problems In Excel 2013 – And What To Do About Them

with 11 comments

Anyone who has tried to do any serious work with Power Pivot and Power Query will know about this problem: you use Power Query to load some tables into the Data Model in Excel 2013; you make some changes in the Power Pivot window; you then go back to Power Query, make some changes there and you get the dreaded error

We couldn’t refresh the table ‘xyz’ from the connection ‘Power Query – xyz’. Here’s the error message we got:

COM Error: Microsoft.Mashup.OleDbProvider; The query ‘xyz’ or one of its inputs was modified in Power Query after this connection was added. Please disable and re-enable loading to the Data Model for this query..

image

This post has a solution for the same problem in Excel 2010, but it doesn’t work for Excel 2013 unfortunately. There is a lot of helpful information out there on the web about this issue if you look around, though, and that’s why I thought it would be useful to bring it all together into one blog post and also pass on some hints and tips about how to recover from this error if you get it. This is the single biggest source of frustration among the Power Query users I speak to; a fix for it is being worked on, and I hope it gets released soon.

Problem Description

Why does this problem occur? Let’s take a simple repro.

  1. Import the data from a table in SQL Server using Power Query. Load it into the Excel Data Model.
  2. Open the PowerPivot window in Excel, then create measures/calculated fields, calculated columns, relationships with other tables as usual.
  3. Go back to the worksheet and build a PivotTable from data in this table, using whatever measures or calculated columns you have created.
  4. Go back to the PowerPivot window and rename one of the columns there. The column name change will be reflected in the PivotTable and everything will continue to work.
  5. Re-open the Power Query query editor, and then rename any of the columns in the table (not necessarily the one you changed in the previous step). Close the query editor window and when the query refreshes, bang! you see the error above. The table in the Excel Data Model is unaffected, however, and your PivotTable continues to work – it’s just that now you can’t refresh the data any more…
  6. Do what the error message suggests and change the Load To option on the Power Query query, unchecking the option to load to the Data Model. When you do this, on the very latest build of Power Query, you’ll see a “Possible Data Loss” warning dialog telling you that you’ll lose any customisations you made. Click Continue, and the query will be disabled. The destination table will be deleted from your Excel Data Model and your PivotTable, while it will still show data, will be frozen.
  7. Change the Load To option on the query to load the data into the Excel Data Model again. When you do this, and refresh the data, the table will be recreated in the Excel Data Model. However, your measures, calculated columns and relationships will all be gone. What’s more, although your PivotTable will now work again, any measures or calculated columns you were using in it will also have gone.
  8. Swear loudly at your computer and add all the measures, calculated columns and relationships to your Data Model all over again.

So what exactly happened here? The important step is step 4. As Miguel Llopis of the Power Query team explains here and here, when you make certain changes to a table in the Power Pivot window the connection from your Power Query query to the Excel Data Model goes into ‘read-only’ mode. This then stops Power Query from making any subsequent changes to the structure of the table.

What changes put the connection to the Excel Data Model in ‘read-only’ mode?

Here’s a list of changes (taken from Miguel’s posts that I linked to above) that you can make in the PowerPivot window that put the connection from your query to the Data Model into ‘read-only’ mode:

  • Edit Table Properties
  • Column-level changes: Rename, Data type change, Delete
  • Table-level changes: Rename, Delete
  • Import more tables using Power Pivot Import Wizard
  • Upgrade existing workbook

How can you tell whether my connection is in ‘read-only’ mode?

To find out whether your connection is in ‘read-only’ mode, go to the Data tab in Excel and click on the Connections button. Then, in the Workbook Connections dialog you’ll see the connection from Power Query to the Data Model listed – it will be called something like ‘Power Query – Query1’ and the description will be ‘Connection to the Query1 query in the Data Model’. Select this connection and click on the Properties button. When the Connection Properties dialog opens, go to the Definition tab. If the connection is in read-only mode the properties will be greyed out, and you’ll see the message ‘Some properties cannot be changed because this connection was modified using the PowerPivot Add-In’. If you do see this message, you’re already in trouble!

image

How to avoid this problem

Avoiding this problem is pretty straightforward: if you’re using Power Query to load data into the Excel Data Model, don’t make any of the changes listed above in the PowerPivot window! Make them in Power Query instead.

How to recover from this problem

But what if your connection is already in ‘read-only’ mode? There is no magic solution, unfortunately, you are going to have to rebuild your model. However there are two things you can do to reduce the amount of pain you have to go through to recreate your model.

First, you can use the DISCOVER_CALC_DEPENDENCY DMV to list out all of your measure and calculated column definitions to a table in Excel. Here’s some more information about the DMV:

https://cwebbbi.wordpress.com/2011/09/17/documenting-dependencies-between-dax-calculations/

To use this, all you need to do is to create a DAX query table in the way Kasper shows at the end of this post, and use the query:

select * from $system.discover_calc_dependency

Secondly, before you disable and re-enable your Power Query query (as in step 6 above), install the OLAP PivotTable Extensions add-in (if you don’t already have it) and use its option to disable auto-refresh on all of your PivotTables, as described here:

http://olappivottableextend.codeplex.com/wikipage?title=Disable%20Auto%20Refresh&referringTitle=Home

http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=26

Doing this prevents the PivotTables from auto-refreshing when the table is deleted from the Data Model when you disable the Power Query query. This means that they remember all of their references to your measures and calculated columns, so when you have recreated them in your Data Model (assuming that all of the names are still the same) and you re-enable auto-refresh the PivotTables will not have changed at all and will continue to work as before.

[After writing this post, I realised that Barbara Raney covered pretty much the same material in this post: http://www.girlswithpowertools.com/2014/06/power-query-refresh-fails/ . I probably read that post when it was published and then forgot about it. I usually don’t blog about things that other people have already blogged about, but since I’d already done the hard work and the tip on using OLAP PivotTable Extensions is new, I thought I’d post anyway. Apologies…]

Written by Chris Webb

September 8, 2014 at 9:30 am

Working With Excel Named Ranges In Power Query

with 25 comments

One of the more recent additions to Power Query is the ability to access data from named ranges in the Excel worksheet rather than an Excel table. I’ve got used to formatting data as tables in Excel because that’s what Power Pivot needs to import data directly from the worksheet, but if you are working with Power Query and a pre-existing workbook then trying to reformat data as tables can be a pain. Also, if you just want to import a single value, for example as a parameter to a query, a table seems like overkill.

(Incidentally, if you’re wondering what a named range is in Excel, there are tons of good introductions to the subject on the internet like this one. You can do loads of cool stuff with them.)

Consider the following Excel worksheet:

image

There are three named ranges here: FirstRange, SecondRange, ThirdRange, and the values in the cells show which range the cells are in. FirstRange consists of two cells in two columns; SecondRange consists of three cells in a single row; and ThirdRange is consists of three, non-contiguous cells. (You can also use this trick to display the names of all contiguous ranges in an Excel workbook, but alas it does seem to work for non-contiguous ranges).

At the moment, the Power Query ribbon doesn’t make it obvious that you can use named ranges as data sources. However you can see all the tables and cells in a worksheet, and even return that list from a query, by creating a blank query and using the expression

= Excel.CurrentWorkbook()

image

Once you’ve done this you can see all the ranges (and also any tables) in the workbook, and click on the table link next to the name to see the data. For example, clicking on FirstRange shows the following table in a new step in the query editor:

image

The expression to get at this table in a single step is:

= Excel.CurrentWorkbook(){[Name="FirstRange"]}[Content]

The range SecondRange in my example is equally straightforward to reference, and you can see its contents by using the expression

= Excel.CurrentWorkbook(){[Name="SecondRange"]}[Content]

image

Unfortunately ThirdRange, which is not contiguous, is a problem: I can only get the first cell in the range. So the expression

= Excel.CurrentWorkbook(){[Name="ThirdRange"]}[Content]

Returns just this table:

image

It would be nice if we could get a list containing the cell values, rather than a table, for ranges like this…

Last thing to mention is that if you do want the value in a cell, rather than a table, you just need to right-click inside the cell in the Query Editor and select Drill Down:

image

This returns the value (in this case the text “Third Range Cell 1”) in the cell you clicked on:

image

This is a much more useful value to return than a table containing a single row/column, if you intend to use a value from a single cell in a named range as a parameter to another query.

You can download the sample workbook for this post here.

Written by Chris Webb

July 22, 2014 at 9:30 am

Posted in Excel, Power Query

Using Slicer Selections In The CubeSet Function

with 15 comments

I had an interesting challenge from a customer yesterday – one of those problems that I’d known about for a long time but never got round to working out the solution for…

Consider the following PivotTable, based on a PowerPivot model using Adventure Works data, in Excel 2010:

image

It shows the top 10 products by the measure Sum of Sales. There are two slicers, and the top 10 shown in the PivotTable reflects the selections made in the slicers. All of this works fine. But what if you want to use Excel cube functions to do the same thing? You can write the MDX for the top 10 products quite easily and use it in the CubeSet() function in your worksheet, but how can you get your MDX set expression to respect the selection made in the slicers?

The solution to this problem is very similar to the trick I showed here – finding the selected items in a slicer is not easy! Here are the steps I followed to do it:

  • Add the slicers for EnglishOccupation and CalendarYear to a new worksheet
  • Go to Slicer Settings and uncheck the box for “Visually indicate items with no data”
  • Add two new PivotTables to the worksheet. Connect one to the EnglishOccupation slicer and put EnglishOccupation on rows; connect the other to the CalendarYear slicer and put CalendarYear on rows.
  • Use the OLAPPivotTableExtensions add-in (which you can download here) to add new MDX calculated measures to each PivotTable. For the EnglishOccupation PivotTable call the measure SelectedOccupations and use the following MDX:
    SetToStr(Except(Axis(0), {[Customer].[EnglishOccupation].[All]}))
    This expression does the following: it uses the Axis() function to find the set of members selected on what Excel thinks of as the rows axis in the PivotTable (actually the MDX columns axis), then uses Except() to remove the All Member from the hierarchy (which Excel uses for the Grand Totals) and then uses SetToStr() to take that set and return the string representation of it. Do the same thing for the PivotTable showing CalendarYear too, calling the calculated measure SelectedYears; the MDX in this case is:
    SetToStr(Except(Axis(0), {[Date].[CalendarYear].[All]}))
    This is what the EnglishOccupation PivotTable should look like:
    image
  • Next, to make things easy, use Excel formulas to get the values from the top cell inside each PivotTable into cells elsewhere in the worksheet, and give these cells the names SelectedOccupations and SelectedYears.
    image
  • Then enter a CubeSet() function into a new cell using the following formula:
    =CUBESET(
    "PowerPivot Data",
    "Topcount(
    [Product].[EnglishProductName].[EnglishProductName].members,
    10,
    Sum(" & SelectedOccupations & " * " & SelectedYears & ",[Measures].[Sum of Sales])
    )",
    "Top 10 Set")
    What this does is use the TopCount() function to find the top 10 Products, and in the third parameter of this function which is the numeric expression to find the top 10 by, it crossjoins the two sets of selected occupations and selected years and then sums the output of the crossjoin by the measure [Sum of Sales].
  • Last of all, build your report using the Excel cube functions as normal, using the CubeRankedMember() function to get each item from the top 10 set created in the previous step.

image

You can download my sample workbook here.

The bad news about this technique is that it doesn’t work in Excel 2013 and Power Pivot. It’s no longer possible to create MDX calculated measures on Power Pivot models in Excel 2013, alas. It will work if you’re using any version of Excel from 2007 on against Analysis Services and, as I show here, Excel 2010 and PowerPivot. If you are using Power Pivot and Excel 2013 it might be possible to create a DAX measure to do the same as the MDX I’ve used here (I’m wondering if the technique Jason describes here will work). It would certainly be possible to use CubeRankedMember() to find each item selected in the slicer, as Erik Svensen shows here, and then use Excel formulas to find the MDX unique name for each selected member and concatenate these unique names to create the set expression that my calculated measures return, but that’s a topic for another post. This really should be a lot easier than it is…

Written by Chris Webb

June 20, 2014 at 10:59 am

MDX Cell Properties Supported By Excel

with 5 comments

I was wondering the other day (as you do) which of the MDX cell properties Excel PivotTables actually support. This page has all the details on the cell properties that are available in an MDX query but most client tools don’t bother retrieving all of them and Excel is no different. Of course it retrieves the most important properties and it retrieves one or two others, but I couldn’t resist doing a bit of research to find out the exact situation with Excel 2013.

The first thing to note is that you can control which cell properties Excel retrieves for a given connection in the connection properties dialog, in the OLAP Server Formatting section:

image

If you can live with not getting all of the cell properties back from SSAS there are some scenarios where unchecking all of the boxes in the OLAP Server Formatting section can improve performance:

  • When you have a large number of databases and cubes on your server, and/or complex security, because a side effect of the way Excel retrieves properties is that it causes all cubes in all databases to be loaded, their MDX Scripts executed and security evaluated. See here for more details.
  • When your queries return a lot of data and your network is slow. See page 61 of the SQLCAT Guide to BI and Analytics for more details.

You can also quite easily see which cell properties Excel is retrieving by looking at the MDX queries it generates (thank you OLAP PivotTable Extensions). Here’s an example of a simple PivotTable query run on a connection which has all of the boxes checked in the OLAP Server Formatted section:

SELECT 

{[Measures].[D1],[Measures].[D2]} 

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME 

ON COLUMNS  

FROM 

[Adventure Works DW2008] 

CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

 

In the cell properties clause of this query you can see the six properties returned. Here’s a breakdown of each of them.

VALUE and FORMAT_STRING

Excel doesn’t actually retrieve the FORMATTED_VALUE cell property, which gives you the measure value with formatting applied by SSAS. Instead, assuming you have the Number Format box checked in the Connection Properties dialog, it retrieves the VALUE property from SSAS (which contains the raw, unformatted measure value) and the FORMAT_STRING property (which contains the format string you defined on the server). It then tries to convert the format string into an Excel format for the PivotTable. Unfortunately it can’t always do the conversion successfully – I blogged about a problem with the Percent format some time ago and this is still a problem with Excel 2013. Excel also doesn’t support formats defined in the fourth section (see here for more details) of the FORMAT_STRING property for null values, and there are probably lots of other relatively obscure types of formatting it doesn’t support too.

LANGUAGE

The language property of a cell controls things like the currency symbol displayed when you are using the “Currency” built-in format string and the thousands and decimal separators used. Whether Excel returns the language property or not is also linked to the Number Format checkbox in the Connection Properties dialog. I strongly recommend that you do not use the “Currency” format string and the Language property if you are working with multiple currencies, for reasons I outlined here.

BACK_COLOR and FORE_COLOR

I’ve never particularly liked using the BACK_COLOR and FORE_COLOR properties on a cell to do traffic light-style reporting, to be honest, although I know some people love it. However I do use these properties a lot when debugging scoped assignments as seen here. BACK_COLOR is retrieved if you have the Fill Color box checked on the connection properties dialog; FORE_COLOR is retrieved if you have the Text Color box checked on the connection properties dialog.

FONT_FLAGS

Probably the only surprise of this whole exercise was the fact that Excel retrieved the FONT_FLAGS property if you have the Font Style box checked on the connection properties dialog; alas it doesn’t support FONT_SIZE or FONT_NAME. For example if you put the following calculated members on your cube:

CREATE MEMBER CURRENTCUBE.MEASURES.BOLD as "This is BOLD", FONT_FLAGS=1;

 

CREATE MEMBER CURRENTCUBE.MEASURES.ITALIC as "This is ITALIC", FONT_FLAGS=2;

 

CREATE MEMBER CURRENTCUBE.MEASURES.UNDERLINE as "This is UNDERLINE", FONT_FLAGS=4;

 

CREATE MEMBER CURRENTCUBE.MEASURES.STRIKEOUT as "This is STRIKEOUT", FONT_FLAGS=8;

If you drop them into a PivotTable, you will see the following returned:

image

Written by Chris Webb

April 25, 2014 at 11:02 am

Power BI Review, Part 2.5: Q&A (Now I’ve Used It With My Data)

with 6 comments

A few months ago I posted a review of Q&A, the natural language query functionality in Power BI, based on the sample data sets that were then available. Last week, finally, we got the news that we could enable Q&A on our own Power Pivot models, and having played with this new release I thought it was a good idea to post an update to my original thoughts.

The first thing to point out is that even if you’ve got a Power BI Preview tenant you will need the latest version of Power Pivot for Excel to be able to get the best out of Q&A. This latest release contains some new functionality to add ‘Synonyms’ to the model – what this means is that it allows you, as a model creator, to tell Power BI about other names that end users might use when querying your model. For example on a Geography dimension you might have a column called State but if you are a multinational company you may find that while your State column contains the names of states in the USA, it might contain the names of cantons in Switzerland, counties in the UK, departments in France and so on. As a result you will want Power BI to know that if a user asks for sales by county in the UK that it should actually look in the State column. Devin Knight has already written a good post showing how synonyms work with Q&A which you can see here.

Another complication is that, at the time of writing, the Synonym functionality is only available to users who have installed the streamed version of Office 2013 from Office 365. I have an Office 365 subscription but I had installed Office from an msi before that, so I had to uninstall Office and reinstall the streamed version to be able to see Synonyms – I assume that support for Synonyms in the non-streamed version of Excel will come at some point soon in the future, but in general I would expect that new Power BI functionality will appear first in the streamed version of Office first so if you’re serious about BI you should change over to it as soon as you can. Melissa Coates has a lot more detail on this issue here.

But enough about setup, what about Q&A? The data that I tested it on was a model I’ve been using for user group and conference demos for about six months now, which contains data from the UK’s Land Registry and details all residential property transactions in England and Wales in 2013. It’s fairly simple – two tables, a date table and a transactions table containing around 0.5 million rows – so probably a lot simpler than the average Power Pivot model, but nonetheless real data and one which had been polished for demo purposes. The Excel file holding it is around 25MB so I was well within the Power BI file size limits.

My initial impression after I had added my existing model (with no synonyms etc) to Q&A was that while it worked reasonably well, it worked nowhere near as well as the demo models I had seen. I then set about making changes to the model and re-uploading it, and these changes made all the difference. Some examples of the things I did are:

  • Changed table and column names. In my model I had already taken the trouble to make them human readable, but this did not necessarily mean they were suitable for Q&A. For example, my main fact table was called ‘Land Registry’, so at first Q&A kept suggesting questions like “How many land registries were there in June…” which clearly makes no sense. Renaming the fact table to ‘Sales’ fixed this.
  • Setting synonyms. Unsurprisingly, this had a big impact on usability in the same way that changing the table and column names did. I found that I had to go through several iterations of uploading the data, writing questions, seeing what worked and what didn’t, and adding more synonyms before I had a set that I was happy with; I can imagine that in the real world you’d need to round up several end users and lock them in a room to see how they phrased their questions so as to get a really good list of synonyms for them.
  • Setting Power View-related properties. This included setting the Default Field Set on a table, so I only saw a few important fields in a meaningful order when Q&A returned a table result; and also Summarize By so that Q&A didn’t try to aggregate year values. All of this makes sense given how closely-related Q&A and Power View are, but even though I had a reasonably ‘finished’ model to start off with I still hadn’t set all of these properties because I knew I was never going to try to sum up a year column.
  • Adding new columns. There were a number of cases where I realised that I, as a human user, was able to make assumptions about the data that Q&A could not. For example the source data records sales of four different types of residential property: terraced, detached, semi-detached and flat. The first three are types of house, but the source data doesn’t actually state that they are types houses anywhere so in order to see the total number of sales of houses I had to add another column to explicitly define which property types were houses.
  • Disambiguation. Probably the most irritating thing about the Bing geocoding service that Power View and Q&A use is the way it always chooses a US location when you give it an ambiguous place name. Therefore when looking at sales by town I would see the town name “Bristol” show up on the map as Bristol, Tennessee (population 24,821) rather than Bristol in England (population 416,400). Creating a new column with town name and country concatenated stopped this happening.

The Microsoft blog post I referenced above announcing Q&A promises that a more detailed guide to configuring models for Q&A will be published soon, which is good news. The important point to take away from this, though, is that even the most polished Power Pivot models will need additional tweaks and improvements in order to get the best out of Q&A.

The big question remains, though, whether Q&A will be something that end users actually get some value from. As a not-very-scientific test of this I handed my laptop over to my wife (who has no experience of BI tools but who has a healthy interest in property prices) to see how easy it was for her to use, and straight away she was able to write queries and find the information she was looking for, more or less. There were a still few cases where Q&A and/or my model failed, such as when she searched for “average house price in Amersham” – the model has a measure for “average price”, it knows about the property type “house” and the town “Amersham”, but “average house price” confused it and the query had to be rewritten as “average price of a house in Amersham”. Overall, though, I was pleasantly surprised and as a result I’m rather less sceptical than I was about Q&A’s usefulness, even if I’m still not 100% convinced yet.

Written by Chris Webb

December 23, 2013 at 12:45 am

Posted in Cloud, Excel, Power BI, Q&A

Ordering Of Named Sets In Excel

with 3 comments

A bit of an obscure one, this, but it’s come up twice this week so worth mentioning. When you define a named set on your SSAS Multidimensional cube, Excel doesn’t respect the order of items in that set by default when you use it in a PivotTable. Consider the following named set defined on the Adventure Works cube (on the Calculations tab of the cube, not in defined in Excel itself):

CREATE SET [MY COUNTRIES] AS
{[Customer].[Country].&[France], [Customer].[Country].&[Canada], [Customer].[Country].&[Australia]};

Note that the countries are in the order France, Canada, Australia. When you use this named set in Excel, this order is overridden and the countries come out in hierarchy order, that’s to say the order that they appear on the Country hierarchy: Australia, Canada, France.

image 

image

How can you stop this? After all, in a lot of cases the order of members in a named set is important. If you have Excel 2010 or Excel 2013 (I believe this option isn’t available in Excel 2007), you need to click on the name of the set in the PivotTable Field List pane and select Field Settings:

image

Then in the Field Settings dialog go to the Layout and Print tab and uncheck the “Automatically order and remove duplicates from the set” option:

image

When you do that, the order of your set is respected:

image

Written by Chris Webb

July 31, 2013 at 2:12 pm

Follow

Get every new post delivered to your Inbox.

Join 3,715 other followers