Kasper de Jonge PowerPivot Blog

One of the most difficult things when using a multi server farm is the security delegation between the servers. In most cases this still will be done using Kerberos, even though we have Claims based authentication in SP2010 most systems outside the native SharePoint 2010 farm will be using Kerberos (like SSAS and SSRS).

Microsoft has released a great whitepaper that:

This document provides you with the information that will help you:

• Understand the concepts of identity in SharePoint 2010 Products
• Learn how Kerberos authentication plays a critical role in authentication and delegation scenarios
• Identify the situations where Kerberos authentication should be leveraged or may be required in solution designs
• Configure Kerberos authentication end-to-end within your environment including  scenarios which leverage various service applications in SharePoint Server
• Test and validate that Kerberos authentication is configured correctly and working as expected
• Find additional tools and resources to help you configure Kerberos authentication in your environment

This document is divided in two major sections:

• Overview of Kerberos Authentication In SharePoint 2010 Products

This section provides conceptual information about managing identity in SharePoint Products, the Kerberos protocol, and how Kerberos authentication plays a key role in SharePoint 2010 Solutions

• Step-By-Step Configuration

This section will walk you through the steps required to configure Kerberos authentication and delegation in various SharePoint solution scenarios.

Download the whitepaper here.

I was searching for some query to get information from my DWH without me having actually access, i found a set of pretty heavily commented queries that are very useful for detecting and diagnosing many common performance issues with SQL Server 2008 created by Glenn Berry

Check them here:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!2015.entry

The PowerPivot twins have created a great overview of all the SharePoint Installation Guides available atm. Since installing it is certainly no walk in the park. The more info the better so read this while planning your install:

#PowerPivot for SharePoint Installation Guides

Thanks to the great discussion some of us had on Twitter and the follow up on the PowerPivotPro blog we now know a PowerPivot API will be available in the future:

Patience guys. The API will be there.

We are just crunched on time to ship V1.0 of PowerPivot (remember – just 18 months from SQL 2008). But we have the full intention of exposing the API in full.

As MS BI consultant i’m really looking forward to PowerPivot but as developer with C# affinity i’m really excited on the prospect of the PowerPivot API. Think of all the potential projects you could do!

I was working with a client where i had some difficult security questions, that i had to solve without having to resort to advanced MDX query’s. The client has to be able to create reports without MDX knowledge. Take the following example from the AdventureWorks cube:

I want to give users a report that show all sales by state province of the country they are in, e.g. you are a sales person belonging to postal code 91801 and you want a report showing all the state-province’s of the United States since that is the country you belong to.
As seen in the dimension below:

But by default users are forbidden to see the sales of another state province when their postal code isn’t in it. When you place security on a country by using a role, autoexists take care of the security for the rest of the levels resulting in only the parents of the selected postal codes will be shown to the user with the role defined.

This will result in not being able to show the parent levels your postal code does not belong to, as seen below while browsing the report using the role:

When you create a report based on this dimension you get the following report (don’t mind the formatting)

The key to solving this problem is using two dimensions with the same levels. One with the security enabled by using a role and one dimension (you can just copy the original dimension) without security, you could remove the postal code level in this dimension to make sure the security is intact.

When you create your report you use levels from both dimensions, and this is where autoexists is your friend. Since you want to show only city’s from a sales persons own country you use the the country level from the secured dimension (only his own country is returned by the secured dimension) and the state province level from the security-less-dimension, autoexists takes care of showing only those state provinces of the country the sales person belongs to.

Resulting in the following desired report:

All in all reasonably simple when you understand autoexists.

SQLCat has a great blog post about installing SSRS with Sharepoint integration:

This technical note describes how we designed and implemented a business intelligence solution that utilized a server farm containing Microsoft® Office SharePoint® Server 2007, Microsoft Office PerformancePoint® Server 2007 Monitoring Server, and Microsoft SQL Server® 2008 Reporting Services in SharePoint mode, all running on Windows Server® 2008 R2 and with all servers and applications configured for Kerberos authentication. In this technical note, we discuss the design requirements for this business intelligence solution, its logical architecture, the challenges we faced in architecting and implementing this solution, and our resolutions to these challenges.

wish we had this sooner, implementing this is NOT easy. See also my colleague Marc Valk excellent blog post about Sharepoint and SSRS integration over multiple servers (not the default all on one box) with the famous double hop issues and how to fix them. Read it here:

http://www.marcvalk.net/2009/04/sharepoint-and-ssrs-integration/

I have developed a report where i have to use data from different datasets, to get these on one report i have to use multiple tablix data regions
(one dataset per tablix). The report must appear as one tablix in stead of multiple. The tablix data regions are placed below each other with the same columns.

And that’s where the layout problems start, I made sure the columns from the tablix data regions have the same width and are aligned by using the alignment grids.
While using preview mode it looks great the columns are aligned perfect, but after publishing the report to the reporting server the layout is all fumbled up. The columns are no longer aligned below each other.

After some extensive debugging and playing around, I found the solution: it appears reporting services renders the preview different from the browser, the solution from my problem was to set the cangrow property of the row to false (the default setting is true).

I understand this is the solution to my specific problem but it can give you a hint to your solution, there are several properties that can alter the layout of your report.

By default, a chart in SSRS will automatically position the labels on the X-axis as it best fits.  However, one of these options includes hiding labels when the chart feels there are too many to show.  As you can guess, this is not always what we want.

A example:

When opening up the axis properties for the X-axis on a column chart you get the Category Axis Properties screen.  Like all other property screens, this is also one with several pages.  You see that one of the pages is called Labels and as you want it to show all labels, that’s were you start looking.  Well, stop looking, that’s the wrong place.  The option that you need is located in the first page, the Axis Options, and its called Interval.  This is the interval between each label on the axis, and by default it is set to Auto.  As we want all labels, change it to 1.

I changed the direction of the labels as well, to rotate 90 degrees:

Found at: http://blog.hoegaerden.be/2009/07/20/chart-optimization-tips/