-
Reporting Services SharePoint Integration in SQL Server Denali
August 14, 2011 / No Comments »
A week ago, I raved about how the new PowerPivot Configuration Tool would simplify configuring PowerPivot for SharePoint. Guess what? Setting up Reporting Services for SharePoint integrated mode got simplified too! In SQL Server Denali, Reporting Services leverages the SharePoint service application infrastructure and it doesn't require installing a Reporting Services server. Not only this simplifies setup but improves performance because there is no round-tripping between SharePoint and report server anymore. Configuring Reporting Services for SharePoint integration mode is a simple process that requires the following steps: Run the SQL Server Denali CTP3 setup and Install Reporting Services and the add-in on the SharePoint application server. Once the bits are installed, open SharePoint Central Administration, and click Manage Service Applications. Expand the New button and click SQL Server Reporting Services Service application. In the page that follows, configure the service application details as per your specific environment and click OK....
-
Transactional Reporting with BISM Tabular
August 8, 2011 / No Comments »
Continuing on BISM Tabular performance (see my Cube vs. VertiPaq Query Performance blog), I was disappointed to find that there are no performance improvements in PowerPivot/BISM Tabular in CTP3 in the area of detailed (transactional-level) reporting which has plagued me since the R2 release. Consider the following report build on top of an Adventure Works PowerPivot model. This report shows all Adventure Works customers and their order information by combining data from the Customer (18,000 rows) and InternetSales (60,000 rows) tables. Specifically, the report includes two columns from the Customer table and four columns from the InternetSales table. The report has a slicer to let the user filter a customer. Given that PowerPivot is an end-user tool it's likely that end users would build such reports. Unfortunately, each time you click the slicer or filter a column, the report query takes about ten minutes (!) to execute. One would surely...
-
PowerPivot Configuration Tool
August 8, 2011 / No Comments »
One great enhancement in SQL Server 11 (Denali) is the PowerPivot for SharePoint setup refactoring. Previously, the PowerPivot for SharePoint setup and configuration was included and performed at the end of the SQL Server setup. Consequently, it was very difficult to figure out what went wrong. In Denali, you need to perform the PowerPivot for SharePoint configuration as a post-installation step after the SQL Server setup completes. You can do this using the new PowerPivot Configuration Tool, SharePoint Central Administration, or PowerShell. You can find the PowerPivot Configuration Tool in the SQL Server Denali CTP3 ð Configuration Tools program group. When you open the tool, it examines the SharePoint setup and presents a list of tasks (if any) that need to be done to configure PowerPivot for SharePoint so it's operational. Once you validate the tasks and run them, the tool would perform the steps one at a time and...
-
Cube vs. VertiPaq Query Performance
July 28, 2011 / No Comments »
This is a big topic and frankly it's too ambitious on my part to tackle it. Assuming equivalent multidimensional (BISM Multidimensional) and tabular (BISM Tabular) models, I was curious how a multidimensional cube fares against VertiPaq in terms of performance. To be fair to VertiPaq, I decided to use native DAX queries. As you've probably heard, BISM Tabular in SQL Denali will include a variant of DAX to query tabular models deployed to SharePoint and SSAS running in VertiPaq mode. Chris Webb has a good writeup about DAX queries here. The DAX EVALUATE construct allows external clients to query tabular models using native DAX syntax instead of MDX. Since BISM Tabular speaks DAX, DAX queries are likely to be more efficient and give you better performance when querying tabular models. At this point, only Crescent generates native DAX queries. The DAX query syntax is: DEFINE MEASURE Table1 [measure1] = <DAX_Expression>...
-
The Load Balancing Act
July 12, 2011 / No Comments »
I had some fun lately setting up a Reporting Services farm of two SSRS 2008 R2 nodes and a hardware load balancer. We followed the steps in BOL only to find out that the report server will return sporadic empty pages or MAC viewstate validation errors although the machine key was identical on both servers. We fixed the issues by: Enabling sticky sessions in the load balancer (not documented). Configuring the ReportServerURL setting (BOL says "Do not modify ReportServerUrl"). Despite what BOL says or doesn't say, it appears that sticky sessions required with R2 probably due to the AJAX-based ReportViewer. Here is an example configuration that demonstrates the three settings you need to change in the rsreportserver.config: <UrlRoot>http://atltstssrsibo/reportserver</UrlRoot> <Hostname>atltstssrsibo</Hostname> <ReportServerUrl>http://atltstbir02ibo/reportserver</ReportServerUrl> On each node, set up ReportServerURL to point to the node itself. In this scenario, atltstssrsibo is the load balancer name and atltstbir02ibo is the node server name.
-
PowerPivot and SQL Server Denali CTP3 Release Notes Available
July 8, 2011 / No Comments »
Microsoft published release notes for PowerPivot and SQL Server Denali Community Technology Preview (CTP) 3 Release Notes. This means that the CTP3 release is imminent although the download link is not live yet. The release notes cover the new features pretty well. UPDATE 7/12/2011 CTP3 got released today.
-
Applied Analysis Services 2008 and PowerPivot Onsite Class
July 3, 2011 / No Comments »
I am partnering with Data Education to deliver an intensive five-day onsite class on Analysis Services and PowerPivot. The class will be held September 19-23 at the Microsoft Technology Center in Boston. The class doesn't assume any experience with Analysis Services. We'll start from zero and build a multidimensional cube sharing along the way as many as best practices as possible. More information about the class and registration details is available here.
-
Using the IF Operator in Scope Assignments
July 3, 2011 / No Comments »
UDM scope assignments are incredible useful because they let you write to the cube space, such as to implement custom aggregation, allocations, currency conversion, data calculations, and so on. Unfortunately, scope assignments have limitations. One of them is that more complicated scope expressions result in "an arbitrary shape of the sets is not allowed in the current context" error. Recently, I tried to use a scope assignment to zero out a cube subspace that the end user shouldn't see and cannot be protected via dimension data security. I tried the following scope assignment (translated to Adventure Works): Scope ( Employee.Employees.Members - Exists([Employee].[Employees].Members, <another set>, "<Measure Group Name>"), <another attribute hierarchy> ); this = null; End Scope; This produces the above error caused by employee set expression and as far as I know there is nothing you can do to rewrite the scope expression to avoid the error. In a moment...
-
Atlanta BI Record Attendance Last Night
June 29, 2011 / No Comments »
We had a blast light night at Atlanta BI and ran out of space with a record attendance of some 60+ people. This is phenomenal given that we are amidst a vacationing season. Jonathan Lacefield from Daugherty gave us a great intro presentation of Analysis Services. Michael Clifford shows cool Integration Services tips. And, Beth Lenoir from Daugherty was kind to sponsor to event and arrange for some great food. Whether it was Jonathan's presentation, tips, or the food, the atmosphere was electrifying. Thanks to everybody for making last night a fantastic success!
-
Performance Considerations for Securing Parent-Child Dimensions
June 23, 2011 / No Comments »
Parent-child dimensions are a very useful feature of Analysis Services to model recursive hierarchies, such as an organizational hierarchy. Recently, I had to apply dimension data security to a parent-child dimension. The allowed set expression used the factless fact table approach (see my Protect UDM with Dimension Data Security article for more details), as follows (translated to the Adventure Works schema): IIF -- check if the user has full access (StrToMember("[User].[Login].&[" + Username +"]").Properties("FullDimensionAccess",TYPED)=True , [Employee].[Employees].[All], Generate ( Exists ([Employee].[ Employee].[ Employee].Members, StrToMember("[User].[Login].&[" + Username + "]"), "Bridge Employee Security") , {LinkMember (Employee.Employee.CurrentMember, Employee.Employees)} ) ) To avoid loading the factless fact table with all dimension members for users with full dimension access, the expression checks if the user is flagged as such. Since Analysis Services doesn't provide a way to ignore dimension data security from the allowed/denied set, the expression returns the hierarchy top member [Employee].[Employees].[All] and this is...
Training
We offer onsite and online Business Intelligence classes! Contact us about in-person training for groups of five or more students.For more information or to register click here!
Books
Learn Power BI at your own pace with our latest book Applied Microsoft Power BI. Targeting information workers, data analysts, pros, and developers, It is designed as an easy-to-follow guide for learning how to implement BI solutions spanning the entire personal-team-organizational BI spectrum.
