Background 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…
Month: January 2010
Reading your #PowerPivot Profiler trace
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. Figure 1: Click the checkbox to enable client tracing In order to view this trace, you will use SQL Server Profiler to view it. Yet, if you do not have a SQL Server 2008 R2 client components…
Create a #PowerPivot report filtered by the Top X Users by <Logic> (Part 2 of 3)
Background 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. Figure 1: Top 10 Users by Events using an Excel Named Set While it is easy to show the Top 10 users, it is…
Create a #PowerPivot report filtered by the Top X Users by <Logic> (Part 1 of 3)
Background 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. Figure 1: Events by…
#PowerPivot Data Refresh SSRS Report
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. Figure 1: PowerPivot Management Dashboard: Data Refresh Web Parts But how about if you wanted to create a SSRS report of the same (or even more) information so that way you can create alerts and/or have an atom feed of this information? …
How do I get the menu options for individual reports within the #PowerPivot Gallery?
When you start playing with the PowerPivot Gallery, some of you will be enamored by the various views of the Silverlight control included as part of the PowerPivot Gallery – I know I was! But if I want to modify or view the properties of the individual report – how do I do this? Well, it’s all about the different views within the PowerPivot Gallery. To change the views go to the Ribbon > Library and under the “Current View” you can change the view of your PowerPivot Gallery (the above is the Carousel view). Choose the Public > All…
Pros/Cons of deploying #SSIS with the SQL engine server
I was asked a pretty good question recently on what are the pros and cons of deploying SQL Server Integration Services packages on the same server that has your SQL Engine Server as well. While we covered a lot in Top 10 SQL Server Integration Services Best Practices, we never really addressed this issue specifically – so let’s do it: Advantages of placing SSIS and SQL on the same server: You can use the SQL Server Destination instead of OLE DB which allows you to load data into SQL faster. You remove network as a bottleneck for insertion of data…
The Great #PowerPivot FAQ is now accepting applications
Just in case you didn’t already know from the tweets, I figured I’d also give a shout out (again) to the “Great PowerPivot FAQ” and note the fact that it is now accepting applications for moderators. To become a moderator – is it a trial by fire? Does one need to know how to unlock the secrets of chair that nearly killed General O’Neill (Stargate Universe reference)? Or does someone need to quote Admiral Adama? (BSG reference) While those are great – the answer is less complicated and more PowerPivot! So to find out… check out the blog posting at…
Denny Lee 