Be aware of this feature when publishing Excel power pivot models to SharePoint

When creating Power Pivot models in Excel and you hide all the columns in your fact table/-s – Excel will show a ∑ next to the table name and show these first in the PivotTable field list in Excel.

This is a good way to help the users of your models to navigate in your model – but if you plan to browse the models via SharePoint – Excel Services has a very bad side effect…

The fact table cannot be accessed via the PivotTable Fields selector…..

If you unhide a field in the fact table

The table will now show as table in the field list in Excel.

And when you browse your model in SharePoint the Facts can now be added to the pivot table in the browser.

So if you plan to publish your models on SharePoint – do not hide all the columns in the fact tables unless you do not want the users to be able to modify the values in the pivot tables.

Setting selected item in a slicer connected to a Power Pivot table via VBA

Yesterday a colleague asked me whether it was possible to select an item in a slicer via VBA and I replied “Of course” – but that was before I studied the documentation about the “Selected” property of the SlicerItem object –

“Returns or sets whether the slicer item is selected. Read/write for slicers connected to non-OLAP data sources. Read-only for slicers connected to OLAP data sources.”

http://msdn.microsoft.com/en-us/library/office/ff822747(v=office.15).aspx

Now that was a big surprise – and I thought why not try to work around this.

First idea was to add a page field (Filter) because changing a page field will set the selected slicer items to the items selected in the page field (Filters)

->

However, writing a macro to do this seemed a bit over kill – so I continued reading about slicers and their properties

Moreover, it turned out that if we look at the SlicerCache it has a property called “VisibleSlicerItemsList”

That is not read/only – but Read/Write

 

Therefore, we can use this property to set the selected item in a slicer connected to an OLAP source – and here is the code to set a selected item

Sub SetSelection()

Dim sc As SlicerCache

Dim s As Slicer

Set sc = ActiveWorkbook.SlicerCaches(“Slicer_DimDate.CalendarYear”) ‘ Name of slicer

Set s = sc.Slicers(1)

sc.VisibleSlicerItemsList = “[Query1].[DimDate.CalendarYear].&[2005]” ‘Name of item you want selected

End Sub

 

Run the code (or modify to your need) to select the item.

You can download an example file here.

Fun with Power Query and public data from Copenhagen Open data

Copenhagen municipality is exposing open data via this site – http://data.kk.dk/ . There are some interesting datasets but also some quite strange ones – for example a dataset containing all the bollards in the city.

Even though the data seems irrelevant at least for me – it is fun to use Power Query to extract the data and then use Power View and Power Map in Excel to visualize the data.

During the process, I learned a nice feature in Power Query that you might find useful as well.

Retrieving the data

The data can retrieved as a CSV file via this link

http://wfs-kbhkort.kk.dk/k101/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=k101:pullert&outputFormat=csv&SRSNAME=EPSG:4326

This data set contains the point for each bollard as a text

I could do a lot of transformation to extract the data but then I noticed that the url contained an parameter called Output format –

http://wfs-kbhkort.kk.dk/k101/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=k101:pullert&outputFormat=json&SRSNAME=EPSG:4326

And by changing it to JSON – I could retrieve the data as a JSON document.

I then clicked the list of records to expand all records

Converted it into a table

Expanded the Column1

Expanded the Column1.properties to get all meta for the bollards

And then expanded the Column1.geometry to get the point information

This gave me a challenge because the coordinates was a list of values containing the Latitude and longitude and when expanding the column the list values will create two rows per bollard.

 

The useful Power Query tip

 

I didn’t want that as I wanted a separate column with longitude and latitude – but this can actually easily be done in Power Query.

Add a Custom Column and add the following formula

Where 1 refers to the row number in the list – Remember its zero based to 1 will be row 2.

And then I have to columns

The rest is renaming columns, deleting unwanted columns and one very important thing specify the Longitude and Latitude columns as decimal numbers otherwise Excel will import them into the datamodel as text values with no option of plotting them in Power View or Power Map.

And then we can start visualizing the data using Power View and/or Power Map.

One import thing about plotting the bollards in Power View is that it doesn’t handle many unique points (locations) – so instead I added the Road Name as location and then Power View will group them on the roadname – then add the Bollard ID in location as well and the user has a drill down option by double clicking the bubble.

In this case I double clicked the Dag Hammerskjölds Alle – with 239 bollards and can see all is placed on the pavement (fortov)

Another options is of course to use Power Map

 

In this case, I modified some settings in order to view the point better.

You can download the file from here – http://1drv.ms/1opZHgq

 

 

 

#PowerPivot Tip – Set the default number format for a measure in your Power Pivot model

When you are creating measures in your Excel Power Pivot model you can do yourself a great favor if you first set the number format on the column in the data table.

In this example I have a SalesUnits column that is a decimal number and has set the format to General – which is the default.

When I click the Autosum button in the task pane to create a measure or enter the formula manually the number format will also be set to general.

But I want the number format to include a thousand separator and no decimals which means I have to format both measures manually.

If I set the format on the column before I start creating measures – in this case format Sales Units

OBS!!! This only works if you use the AutoSum button.

So if you want to create measures with CALCULATE etc. start by clicking the AutoSum – and then modify the measure and you have the number format in place.

Hope this can help you.

 

 

 

 

 

 

Format the week number in your Power Pivot date table

When creating your date table in Power Pivot the users typically want a Week label containing the week number and the year – 2014 and week 14

 

 

If you create a calculated column in your table and use the format code ” 00″ in the TEXT function second parameter

You can do the same in Power Pivot by adding a calculated column

 

But in Power Pivot you have to use the FORMAT function instead of the TEXT function.

Analyzing POS data using the new Q&A function in Power BI

The Q&A feature in Power BI has so far been limited to the samples files provided by Microsoft – but now you can use your own models.

I have played around with the feature and here is the result. Click the picture to see a video of a demo.

I think it’s pretty awesome and will open up new possibilities for the BI users – It will be very exciting to try to see how the users will feel about this new tool.

Update only selected tables in your Excel Power Pivot Model

Here is a quick tip to update selected tables in your Power Pivot model instead of doing it one by one or via the “Refresh All” option.

If you switch to Diagram View and select the tables you want to update while holding the CTRL key – and then click Refresh – This will only update the selected tables.

In this case I have selected the Sales, Chain and Product table

And voila – 3 out of 6 tables updated

 

Hope this can save you some update time.

How to list only selected slicer items in separate slicer

My most popular post so far has been “How to get selected items in a slicer without VBA” and I have demoed the solution at SQL Saturday and Campus days in Copenhagen.

This solution makes heavy use of cube functions and can be fairly complicated to construct and maintain – so I decided to see if it was possible to do try and see if I could list the selected items in a separate slicer.

So the slicer “Selected weeks” lists the selected items and I can actually copy this slicer to all the sheets where I have reports that is connected to the first slicer “Week”.

Here is how I did this

First Step – Create a DAX Query table

In my workbook with my powerpivot model I added a DAX Query table to the time table via Data tab – Existing Connections and found the Time table in my list of tables

Clicked on Open and selected a Table

This gives me a standard Excel table linked to the Power Pivot model as a DAX Query.

 

Calculate selected items

 

Next to the table I added a column called “Selected Weeks”.

And I added the following formula

=IF(ISNUMBER(CUBEVALUE(“ThisWorkbookDataModel”;Slicer_YW1;CUBEMEMBER(“ThisWorkbookDataModel”;”[Time].[Week].&[“&[@Week]&”]”);”[Measures].[Sales Value]”));[@Week];””)

The formula uses the CUBEVALUE function to calculate the Sales Value for the Slicer “Slicer_YW1” and the current member of [Time].[Week] – if the current week intersects with one of the selected items in the slicer – then it will return a value/number and if so – the formula will return the current week label – otherwise a blank text.

Create the Slicer

Then I can use the “Insert Slicer” on the table to insert a slicer for the column “Selected weeks”

 

And I get a slicer containing the selected weeks in the primary slicer

Clean up the blank

As all the rows that wasn’t selected items is set to return a blank – the slicer will have an item with a blank item – this can be avoided as well.

First of this table should only be used for this purpose so we actually don’t care what the blank value is – and as we now that slicers return unique items we can instead of returning a blank item – we can return one of the selected items.

So above the table I create a formula to get the first selected item in the primary table.

By using the formula

=CUBERANKEDMEMBER(“ThisWorkbookDataModel”;Slicer_YW1;1)

And modify the fomula in selected weeks to

=IF(ISNUMBER(CUBEVALUE(“ThisWorkbookDataModel”;Slicer_YW1;CUBEMEMBER(“ThisWorkbookDataModel”;”[Time].[Week].&[“&[@Week]&”]”);”[Measures].[Sales Value]”));[@Week];$F$2)

And the blank member is now gone

 

And I have a nice clean slicer with the unique items selected in the “Power Pivot” slicer – and as this is linked to a standard Excel table – I can copy this to all my report sheets where I want to display the selected weeks – that I typically let them select on a central Filter/Slicer Sheet.

 

 

 

How to easily limit the number of rows in your Excel Power Pivot model

I am currently working with an Excel Power Pivot model with about 20+ million rows in the model, and as there are a lot of Excel reports in the model and some of the calculated fields are pretty complex it takes some time when I move calculated fields, format them or change them.

The model retrieves data from a SQL database so the table queries are either Stored Procedures or handwritten SQL statements.

So in order to quickly reduce the number of rows in my biggest fact table I have added

SET ROWCOUNT 2000

Before my in this case Stored Procedure

And it validates

 

And after saving and thereby and update – I only have 2000 rows in my fact table

And then when I need the full dataset I use

/* statement */ to turn it into a comment

 

By the way use the /**/ to add comments to your queries so you create short description on what your query is intended to do.

Your first table choice in Power Pivot will haunt you – or how to set the default table name

In Excel 2013 the way to add Calculated fields changed a bit from the first version.

Now you click Power Pivot tab and click Calculated Fields and New Calculated Field. I preferred the old method where I could right click in the Power Pivot fields list and add or modify the measure directly to a table.

In the new I have to remember to set the correct table name in the first drop down box. In this case “Product” is the default table.

And yes – I tend to forget to change it sometime and my calculations typically ends up in the first table in the list.

So I decided to see if I could change it and then I found out that changing the order in the power pivot window had absolutely no effect on the default Table name and then it hit me.

That the Table name in the Calculated Field dialog was the FIRST table I imported in my model.

So if you want to control the Default table name – Start by importing the table you want as your default table for calculated fields.