Adatis BI Blogs

Importing multiple files into Power Query

You can automatically append data from multiple files by choosing to place them in a folder and query the folder. You can choose a folder and it will also search subfolders. You can drop more files into the folder (or remove some) at a later stage, refresh the query and the new files will appear automatically. Once you select From Folder you will be asked to specify the folder location with the option to browse to it. Once selected, Power Query will find suitable files and list them in the query editor window. There will be a column named Content. Like the name suggests this is the contents of the file. There are other columns with details of the file like the name, extension and file path. There is also an attributes column that can be expanded to expose more properties. At the top of the Content column there is a button. If you click this it will import your files and combine them. However when you use this method, you lose all the information about the files themselves and are left with just the contents. What if you want to use the information about the file? To get around that you can create a custom column like so: This example is using csv files. Creating this adds a table object into each row of the custom column. From there you can expand the contents alongside the file information. The same can be done for files stored on SharePoint. You will be prompted to provide the URL for the SharePoint site. This will be the root URL. When you do this, a navigator window will open up but your files won’t appear. If you click on the root and click edit you can see why in the query editor. You’ll notice that the Source query step is in the form of this: = SharePoint.Tables("https://SharePoint/Site/") Change this to SharePoint.Files. This will list all the files within the site! Now you can filter this list by file extension or by Folder Path (using the ‘Begins with’ Text Filter), for example.

PowerPivot Settings for Power View

I’ve been using both PowerPivot and Power View quite a bit recently and, in addition to the post I did a while back, have made a few further observations on getting the two to play nicely together. Building an Example PowerPivot Model For this post I’ve created a very very simple PowerPivot model, based on the Adventure Works Internet Sales Fact table and its related tables. What this means is that after the model is built, I can build a variety of Excel reports that summarise the measures in the FactInternetSales Adventure Works fact table, such as Sales Amount, Tax Amount or Order Quantity. In the example below I’ve built a very basic report showing Order Quantity by year: On to Power View Having already published the workbook to SharePoint, my task now is to create a Power View report, which can be done via the PowerPivot Gallery. If wanted to try and create a visual version of the above report in Power View (E.g. a graph), my steps would be to do first of all drag Calendar Year into the report, then Order Quantity. However, when doing this recently, the result wasn’t quite what I expected: Order Quantity has not been summed at all, which is the opposite behaviour to that of PowerPivot. Consequently I can’t create a graph visualisation, because Power View thinks I don’t have any measures in my report: Yet if we contrast this to measures such as Internet Sales Amount or Tax Amount, they all work fine. Upon looking at the field list for Internet Sales, we will see that Order Quantity is missing the sum symbol, as Power View has not interpreted it as a measure: We can get around this in Power View with a bit of dragging and dropping, but we may as well look why it’s happened in the first place. The reason for this is the way in which the PowerPivot import wizard interprets SQL data types. The OrderQuantity column in the FactInternetSales SQL table has a data type of Smallint, which will not be summarised by default in Power View. Decimal and Money types are fine, hence why Tax Amount and Sales Amount above look ok. Rather than changing the data types, we can force Power View to interpret the column as a measure by going to the PowerPivot advanced tab and clicking on Summarize By. If the Summarize By value is changed from Default to Sum, then Power View will interpret the column as a measure: If we republish the model and then go back to Power View, we will see quite a difference with the same actions as before. First of all the field list is looking correct: Secondly, only dragging Calendar Year and Order Quantity will now give the following correct results: Now that we have a measure, it means that we can now change the table into a visualisation of our choice: Power View is the kind of product that power users are hopefully going to find intuitive, but we can make things even easier for users by making small changes like this.

PerformancePoint Services 2010 – First impressions

We’ve been lucky enough (or we rather hassled enough people in MSFT for long enough!) to have been participating in the Office 2010 technical preview for the last couple of months but as it’s all been under NDA haven’t been able to blog about it.  This also means that we’ve had a chance to look round SharePoint 2010 and, in particular for me, PerformancePoint Services.  Nick Barclay has just done a series of posts about what’s new/improved/different in the new version so go there for the full list.  Here’s a quick round up of our first impressions: What looks good so far: SharePoint integration – Whilst Dashboard designer is still pretty much the same product for doing your, errr, dashboard design it’s no longer the admin and security tool as well.  This is all carried in SharePoint and in fact you have to set up a specific PPS site to do this.  A great deal of effort has obviously gone into this and which looks to have paid off. Security is all through SharePoint – no need to set up permissions twice! AS Conditional formatting now works. Decomp tree is back!! Measures can now be formatted independently. Workspace browser is now much more intelligently organised. Filter by value – you can now restrict rows\columns by value Dynamic dimension measures on scorecards- this was a bit of a workaround in 2007 as I’ve posted about previously. This now works properly Re-usable filters – Filters can now be shared and re-used across dashboards Disappointments: Lack of improvements for data visualisation – Very disappointing - other than the decomp tree, the visualisation side of PPS has changed little.  Still no real control over how your graphs look. The only other new item to be introduced is the Pie Chart!!!! oh dear.  Still no bar charts (and I mean Bars not Columns), no chart formatting options or (controllable) second y axis options that I can see :( Decomp tree is not a chart type but a right click option from a deployed report.  i Like the option to do this from any point in a report but would be nice to have both options. It’s still called PerformancePoint! – I have to admit I when I read another blog of Nick’s following the demise of Planning I didn’t entirely agree with him that it should be renamed.  Having spent the last ten months trying to explain to various IT departments that PerformancePoint is not the devil and that the Monitoring side has not been affected (usually to no avail) has changed my opinion completely.  As per Chris’s blog – ProClarity just seems to have disappeared – I know that was never what Monitoring was supposed to be but the lack of an ad-hoc cube browser is a huge oversight. Did I mention the lack of data visualisation improvements???? There’s lots more to discuss and there will be more to come over the next few weeks time allowing.  SharePoint 2010 looks pretty impressive…

Reporting Services features not supported in Sharepoint Integration Mode

The latest version of SQL Books Online details the features of Reporting Services that are available in Sharepoint Integration mode and, more importantly, the features that aren't!!  Be careful if you plan to migrate as there are some surprising omissions: URL addressing is different in SharePoint integrated mode. SharePoint URLs are used to reference reports, report models, shared data sources, and resources. The report server folder hierarchy is not used. (This basically means no querystring parameters!!!) Reporting Services custom security extensions cannot be deployed or used on the report server. The report server includes a special-purpose security extension that is used whenever you configure a report server to run in SharePoint integrated mode. This security extension is an internal component, and it is required for integrated operations. Report Manager or Management Studio cannot be used to manage a report server instance that is configured for SharePoint integration. Data-driven subscriptions are not available. This applies to all editions. The rs.exe command line utility is not supported. The utility does not support the SOAP endpoint used for programmatic access to a report server that runs in SharePoint integrated mode. Linked reports are not supported. My Reports is not supported. Job management features that allow you to stop a long-running report process are not supported. Batching methods will not be supported. I haven't tested it yet but I have a feeling this might mean that the PerformancePoint Planning operational reports may not work in this mode The online (!) Books Online article is here

PerformancePoint Planning Assignment Web Part for SharePoint

 Update: Version 1.1 is now available  So the Adatis dev team have been busy! As I mentioned in a previous post, one of our clients wanted to be able to show a user's active PPS Planning assignments in their Sharepoint Portal in the same way you can see them in the assignments pane within Excel when you have the add-in installed.  "No Problem!" we said naively, assuming it wouldn't be too tricky as everything is passed back and forth using the web service.   Anyone who has developed .Net Web Parts will know it's a little convoluted, particularly if you want incorporate a decent user interface.  Calling the  Planning Web Service itself was relatively straight-forward though a couple of things were far from obvious and required work-arounds. One of the development requirements was that none of the PPS dll's should need to be installed on the server as it is purely a SharePoint box.  This made things a little more tricky as there is a bit more going on in the PPS excel add-in than just calling the web service!  It also needed to be configurable using the web part property editor as well using allowing users with the PPS Add-in installed to click on the hyperlink and open the assignment in Excel Anyway a few weeks (a few late nights and a few more grey hairs) later it's ready for a beta release: There is currently no documentation around the planning web service so we took a good look round the dll's that get installed with PPS using a great freeware product called Reflector and managed to piece together what was going in Excel when you connected to the PPS Server.  If anyone would like to try the web part please drop us an e-mail and we'll be happy to send you a copy (with the usual disclaimers in case your server falls over in a heap!) in return for some testing and feedback.  We've only had a limited amount of time to test it so any help is much appreciated.  Once it's stable we'll put it on our Web site for download. Thanks to Bully for just a little bit of help with the code ;).  Thanks also to Kevin White and Scott Heimendinger at Microsoft for some pointers in the right direction.

PerformancePoint Server Protocol Handler

Update: The web part is now in beta test - more here When you install the PerformancePoint add-in for Excel it also installs the PerformancePoint Server protocol handler.  This allows you to send out hyperlinks for assignments in e-mails etc so that users can simply click on the link and open their assignment in Excel.  The links are in the format: PerformancePoint:http:\\PPSServer:46787&Application=MyPlanningApp&Assignment=1234 However despite the strong links between SharePoint and PerformancePoint you can't put these into PPS hyperlinks into a SharePoint links list as the links won't validate with PerformancePoint: tagged on the front.  And of course this list wouldn't be dynamic based on the users own assignments. To overcome this, we are currently working on a SharePoint custom webpart for one of our clients that will automatically pick up the users personal assignments.  If anyone is interested drop me a line and I'll be happy to send you a copy once it's a bit more complete. Happy Halloween!!