Cube Formulas and NamedSets Running Commentary

Published February 4, 2010 Perspectives Leave a Comment
Tags: , ,

For those of you who want to know the timeline of the commentary by @PowerPivotPro and @dennylee on cube formulas and named sets, here it is – it started with @dennylee’s three-part posting on how to create filtered by Top 10 reports:

which was quickly responded to and openly challenged by @PowerPivotPro

Solid punches were given and mayhem ensued, but @dennylee got his footing back and wreaked havoc via pile driver!

Perhaps more mayhem will ensue – perhaps not.

Either way, you have to admit its sort of dorky, eh?! ;-)

Create a #PowerPivot report filtered by the Top X Users by (Part 3 of 3)

Published January 31, 2010 Calculation Leave a Comment
Tags: ,

This blog posting is a continuation of previous post, Create a #PowerPivot report filtered by the Top X Users by (Part 1 of 3), where I had created an Excel Named Set based on the Top 10 Users by Events as noted in Figure 1.  What is handy about using named sets is that it will only render the top 10 rows as opposed to the millions of rows of users this data set has.  While it is easy to show the Top 10 users, it is not straightforward to filter by those Top 10 users because Excel Named Sets cannot be used as a filter.  Example reports with  this type of business logic include “most common hospital departmental systems that the top 10 users are accessing” or “what are the most watched TV programs of the top 10 markets (i.e. cities)”.

image

Figure 1: Top 10 Users by Events using an Excel  Named Set

In the second blog posting of this series, Create a #PowerPivot report filtered by the Top X Users by (Part 2 of 3), I showed how to filter by this same named set in a static fashion.    In this third part of this blog series, let’s figure out how to filter by a named set in a dynamic fashion.

Read more …

Reading your #PowerPivot Profiler trace

Published January 30, 2010 Internals Leave a Comment
Tags: ,

To help troubleshoot your PowerPivot for Excel workbook, you can click on Settings within the PowerPivot selection in the Office Ribbon which will bring up the Support & Diagnostics tab within the PowerPivot Options & Diagnostics dialog.  Click on the checkbox next to “Client tracing is enabled” and after opening the PowerPivot window, you will have a trace recorded on your desktop.

Read more…

What database support is needed for #PowerPivot

Published January 30, 2010 Installation Leave a Comment
Tags: ,

By powerpivotgeek (dwickert@microsoft.com), on January 29th, 2010

I get this question a lot and I thought that it was a good one to address here in the blog.

If you look at the PowerPivot for SharePoint architecture, you know that we use several SQL Server 2008 R2 components within the architecture, but how about SharePoint itself . . . What RDBMS does PowerPivot with SharePoint 2010 use? and does it need to be R2?

Read more…

Create a #PowerPivot report filtered by the Top X Users by (Part 2 of 3)

Published January 28, 2010 Calculation Leave a Comment
Tags: ,

This blog posting is a continuation of previous post, Create a #PowerPivot report filtered by the Top X Users by (Part 1 of 3), where I had created an Excel Named Set based on the Top 10 Users by Events as noted in Figure 1.  What is handy about using named sets is that it will only render the top 10 rows as opposed to the millions of rows of users this data set has.

image

Figure 1: Top 10 Users by Events using an Excel  Named Set

 While it is easy to show the Top 10 users, it is not straightforward to filter by those Top 10 users because Excel Named Sets cannot be used as a filter.  Example reports with  this type of business logic include “most common hospital departmental systems that the top 10 users are accessing” or “what are the most watched TV programs of the top 10 markets (i.e. cities)”.

Read more…

Create a #PowerPivot report filtered by the Top X Users by (Part 1 of 3)

Published January 27, 2010 Calculation Leave a Comment
Tags:
January 27, 2010 by dennyglee
I was recently working with a customer to help them solve an interesting PowerPivot problem where they had wanted to get the Top 10 users within their data set and create reports based on that.  One approach is to create a rank function within PowerPivot as noted in a Rank function thread on the SQL Server 2008 R2 PowerPivot for Excel forum?  Another way is to create a Pivot Table where the UserID was placed in the [rows] and the measure in question in the [data] area; and sort the data from largest to smallest.

How to start debugging a PowerPivot for SharePoint installation issue

Published January 22, 2010 Installation Leave a Comment
Tags: ,

By Lee Graber (leegr@microsoft.com), on January 22nd, 2010

A number of people have had issues with PowerPivot installation failures in CTP3. Hopefully, we have resolved all of these for RTM, but I can tell you that setup is still a bit tricky. To give you something of an understanding, the “New Farm” installation option is responsible for getting your machine up and running with as few steps from you as possible. It installs SQL Relational Engine & AS, then configures SharePoint using the newly installed Relational Engine, then configures AS integration with SharePoint. In can be especially tricky in the case of patching and so forth since technically speaking SQL Server does not have to be running to be patched but we cannot interact with SharePoint if SQL Server is not running. We have done a lot of work to try and get this right, but I am not foolish enough to think that there will be no issues. Any issues you hit should be reported to the appropriate forums and perhaps followed up with CSS if necessary, but to get you started on installation issues, this is what you want to look at:

Read more…

Could not load type ‘Microsoft.AnalysisServices.SharePoint.Integration.ReportGalleryView’.

Published January 20, 2010 Configuration , Internals Leave a Comment
Tags: ,

By Lee Graber (leegr@microsoft.com), on January 20th, 2010

A number of people have hit this issue and so it deserves a quick walkthrough of why it (unfortunately) can happen and how you fix it.

The error occurs when you try to select a Document Library of type PowerPivot Gallery (sometimes referred to as Report Gallery). After installing Power Pivot for SharePoint, all new site collections you create have the option to create a new document library of this type and if you use the PowerPivot Site template when creating the site collection, we automatically add a library of this type (that is the main thing we do in this template).

Read more….

PowerPivot Data Refresh SSRS Report

Published January 19, 2010 Configuration , Usage Leave a Comment
Tags: ,

January 19, 2010 by dennyglee

As many of you already know, in order to figure out which workbooks had succeeded or failed execute in their data refresh, you need only to go the PowerPivot Management Dashboard (Central Administration > General Application Settings > PowerPivot Management Dashboard) in order to see the status of your data refresh as noted in the figure below.

How to manually update your PowerPivot Management Dashboard

Published January 19, 2010 Configuration Leave a Comment
Tags: ,

By powerpivotgeek (dwickert@microsoft.com), on January 18th, 2010

We have gotten lots of feedback about the new CTP3 PowerPivot Management Dashboard feature. Most folks are excited about the new reports and can see how the reports give you an unparalleled ability to ‘peek’ inside the running services. However the default “wait-a-day” update cycle can see a bit long. A day-long cycle period seems OK for long-term analysis of data, but for demoing the system and investigating how to use the capabilities work you might want a shorter cycle, or want the ability to refresh the data during the day. This post is all about how you do that.

First, you have to determine if the Usage file collection is long enough for you to wait. By default, it takes 30 minutes for your actual usage of the system (clicking here; clicking there) actually end up in a Usage file. If you can wait 30 minutes, then leave the system as it is. If you need usage information sooner, then you will need to tweak two settings:

 Read more…

Next Page »

Denny Lee’s Tweets (1/2 of PowerPivot Twins!)

Copyright

Copyright © 2010 Denny G Lee - All Rights Reserved