Posts

PowerPivot for Excel SQL Server 2012 RC0 is Here

Microsoft just published the RC0 (release candidate) bits of PowerPivot for Excel SQL Server 2012. Kudos for Microsoft for making the RC bits public this time. Stay tuned for an announcement for the arrival of the SQL Server 2012 RC bits soon. The RC build is a feature-complete and stable build. This will be the last pre-release build until SQL Server 2012 officially ships next year.

Here are all SQL Server 2012 RC0 links pulished so far:

Microsoft SQL Server 2012 Express RC0

Microsoft SQL Server 2012 Semantic Language Statistics RC0

Microsoft SQL Server 2012 Master Data Service Add-in for Microsoft Excel 2010

Microsoft SQL server 2012 Manageability Tool Kit RC0

Report Server Settings in SQL Server 2012 SharePoint Integration Mode

111611_0119_ReportServe1Well, rules have changed. As Jaime Tarquino from the SSRS team explained in his blog, “SQL Server 2012 Reporting Services SharePoint Integrated Mode”, there is no rsreportserver.config file anymore moving to Reporting Services 2012 in SharePoint integration mode (there are no configuration changes with native mode). Instead, the configurations sections are now saved in the SharePoint configuration database.

Problem: A customer plans to upgrade to SQL Server 2012 and SharePoint integration mode. They had the following custom renderer registered in the rsreportserver.config file that uses device information settings to customize the Excel renderer to suppress report headers and use Excel headers instead:

<Extension Name=EXCELNOHEADER Type=Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering>

    <OverrideNames>

        <Name Language=en-US>Excel (no Header)</Name>

    </OverrideNames>

    <Configuration>

        <DeviceInfo>

            <SimplePageHeaders>true</SimplePageHeaders>

            <RemoveSpace>0.012in</RemoveSpace>

        </DeviceInfo>

    </Configuration>

</Extension>

The customer claimed that this configuration is so important that there is no way they could upgrade if this doesn’t work in SQL Server 2012. And after ensuring them that this is an easy fix, I’ve found myself spending hours to understand what needs to be done. On the positive side, I’ve picked up a few PowerShell skills along the way which may come handy given that PowerShell is omni-present nowadays.

Solution: In SQL Server 2012 (SharePoint integrated mode), you need to make such changes with PowerShell. The PowerShell Cmdlets topic in BOL is a good writeup about the SSRS cmdlets. The following script gets the job done:

Add-PSSnapin Microsoft.SharePoint.PowerShell

$apps = Get-SPRSServiceApplication

foreach ($app in $apps)

{

New-SPRSExtension -identity $app -ExtensionType “Render” -name “EXCELNOHEADER” -TypeName “Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering” -ServerDirectives “<OverrideNames><Name Language=’en-US’>Excel (no Header)</Name></OverrideNames>” -ExtensionConfiguration “<DeviceInfo><SimplePageHeaders>true</SimplePageHeaders><RemoveSpace>0.012in</RemoveSpace></DeviceInfo>”

}

The script starts by importing the Microsoft.SharePoint.PowerShell snapi (you don’t need it if you run the remainder of the script in the SharePoint 2010 Management Shell). Then, the Get-SPRSServiceApplication cmdlet returns all the Reporting Services Service Applications available in the farm. For each application, I call the New-SPRSExtension to register the new renderer. To run the script:

  1. Remote to the SharePoint server. You must have SharePoint Farm Administrator rights to run the script.
  2. Open SharePoint 2010 Management Shell from the Microsoft SharePoint 2010 Products program group. Or, if you have installed the Windows PowerShell ISE feature, you can open Windows PowerShell ISE from the Accessories program group but make it sure to run it as administrator (right-click on it and click Run As Administrator).
  3. Copy and paste the script, and hit Enter to run it. The figure below shows the script in Windows PowerShell ISE:
  4. Do iisreset to apply the configuration changes.

For a full list of the New-SPRSExtension cmdlet parameters, you can execute the following command

Get-help New-SPRSExtension – full 

To verify that the changes are applied you could use one of the following two approaches:

  1. Execute the following script to output the configuration of all extensions:
    $apps = Get-SPRSServiceApplication

foreach ($app in $apps) {

    $extensions = Get-SPRSExtension -identity $app

    foreach ($extension in $extensions) {

echo $extension.ExtensionType $extension.Name

echo $extension | select -ExpandProperty ConfigurationXml }

2. In SSMS, connect to the SharePoint config database and run the following query. Examine the content of the Properties column to make sure that the custom extension is registered.

SELECT * FROM [dbo].[Objects] WHERE properties like ‘%excelnoheader%’     

I was tempted to try modifying directly the Properties column in the Objects table and add the new extension section but I realized I had better use of my time than troubleshooting failed SharePoint installations so I decided to stick with the supported way.

Special thanks to Prash Shirolkar from the Reporting Services team for shedding light in dark places. His blog has valuable insights about Reporting Services and SharePoint.

Business Intelligence Edition in SQL 2012

To expand on my announcement about SQL Server 2012 editions, I welcome the new BI edition for the following reasons:

  1. The BI edition will include all BI features, including features that would previously require an enterprise edition, such as SSAS partitioning and semi-additive functions, as well as SSRS data-driven subscriptions. Therefore, from a functionality perspective, there will be no difference between BI and Enterprise editions as far as BI is concerned. That’s said, the Enterprise edition includes a set of database engine features not found in the BI edition, such as partitioning, ColumnStore indexes, AlwaysOn, etc.
  2. The BI edition could be a cost-effective alternative for ISVs and smaller deployments. For example, the Enterprise edition will cost you over 50K with a two-socket 4-core server. However, the BI edition will cost you $8,592 + Nx$209, where N is the number of users. So, for 20 users, it will be less than 13K. The cost convergence with this hardware configuration is about 200 users. Above that number Enterprise will be more cost effective, unless of course you decide to upgrade to say 4-socket server in which case you need to upgrade your Enterprise license.
  3. Once you’ve purchased a CAL for a user, that user can access multiple licensed SQL servers. So, if you install SharePoint and Analysis Services in SharePoint integration mode on one server, which is licensed using the above formula, and then you install SSAS in Multidimensional or Tabular mode on a separate server, you need to pay only $8,592 for the second server. You don’t need to purchase new CALs. Per-user pricing allows you to spread the implementation across the appropriate number of machines without paying a significant price.

SQL Server 2012 Licensing and Editions

Microsoft has spoken about SQL Server 2012 editions and licensing. We now have a brand new BI edition.

http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx

Reporting Services Data Alerts

SQL Server 2012 introduces a Reporting Services feature called Data Alerts. Data alerts notify a selected list of recipients about data changes on the report. Behind the scenes, data alerts use the report data feeds feature which was introduced in SQL Server 2008 R2. Consequently, similar to subscriptions, data alerts run in the background and poll the reports for changes. For more information about the data alerts architecture, read the Data Alerts (SSRS) topic in BOL.

The steps to create a data alert for the Company Sales 2008 report follow (note that creating alerts require View Items and Create Alerts SharePoint permissions):

  1. Open the report in SharePoint.
  2. Expand the Actions menu and click New Data Alert.

    103011_0010_ReportingSe1

    Reporting Services opens the New Data Alert dialog box. The Report Data Name drop-down list shows all data regions defined on the report. In the case of the Company Sales report, there is only one data region – Tablix1. In addition, Reporting Services shows you the data that feeds the region based on the report parameters selected.

    103011_0010_ReportingSe2

  3. In the right pane, click the Add Rule button to specify criteria for activating the alert. In my case, I want the alert to be triggered for the Accessories product category, Bike Racks product subcategory, year 2007, quarter Q4, and Sales greater than 70,000. As the report data shows, currently the Sales value for these criteria is 60,883.20. As new data is coming in, this value will probably increase and I want to be notified after it exceeds 70,000. I set the alert schedule to run on a daily basis (other options include Weekly, Hourly, or Minute).
  4. Next, you specify the e-mail addresses of the recipients who will get notified when the criteria are met and the e-mail subject text. Once you click Save, the alert is saved in the Reporting Services database and scheduled for execution with the SQL Server Agent. I can see and manage the alerts I’ve created by expanding the report context menu in SharePoint and clicking Manage Data Alerts.

    Reporting Services data alerts have the following limitations:

    1. They are only available in SharePoint. Reporting Services in native mode doesn’t support data alerts.
    2. They are supported with operational reports only, such as reports created by Report Designer or Report Builder and deployed to the SharePoint. Data alerts cannot be created on Power View reports.
    3. Similar to subscriptions and report caching, data alerts require stored credentials. Consequently, developers targeting BISM Multidimensional or Tabular must go through the same hoops (none of them easy) to use alerts with Analysis Services. If you are not happy about this limitation, please vote for my suggestion on connect.microsoft.com that is time for Microsoft to address this limitation.

SQL Server 2012 data alerts extend the Reporting Services capabilities to poll reports and trigger notifications when report data changes. If you target data sources that require standard security (login and password), the feature is very easy to use and business users will probably love it.

BISM Tabular Dynamic Data Security Over Bridge Table

About four years ago, in my article Protect UDM with Dimension Data Security, I introduced an approach to secure dimension data in multidimensional cubes by using a factless bridge table. Since then, many BI practitioners have favored this approach because of its simplicity, reusability (transactional reports can join to the security table), and performance. Now that organizational tabular projects support data security, I wanted to try it with BISM Tabular. As it turned out, the factless bridge table approach worked out just fine.

Suppose that you’ve build a tabular project on top of the Adventure Works DW database and the SecurityResellerFilter table is the bridge table that stores the resellers that an employee is authorized to view. As shown in the diagram, SecurityResellerFilter has two relationships SecurityResellerFilter[EmployeeKey] and Employee[EmployeeKey] and SecurityResellerFilter[ResellerKey] and Employee[ResellerKey]. You’d probably want to hide the SecurityResellerFilter table so the end users can’t browse it.

092811_1402_BISMTabular1

Given this model, implementing a dynamic data security over SecurityResellerFilter requires setting up a new role (click the Roles button in the Analysis Services toolbar) that uses a row filter.

092811_1402_BISMTabular2

A row filter defines a filter expression that evaluates which rows the role is allowed to see. To set up a row filter in the Role Manager, enter a DAX expression next to the table name. The DAX expression must be a Boolean expression that returns TRUE or FALSE. The Adventure Works role uses the following DAX expression for the row filter on the Employee table:

=CONTAINS(RELATEDTABLE(SecurityResellerFilter), SecurityResellerFilter[EmployeeKey], LOOKUPVALUE(Employee[EmployeeKey], Employee[LoginID], USERNAME()))

The LOOKUPVALUE function is used to obtain the employee key associated with employee’s Windows login which we get from the Username function. Because the row filter is set on the Reseller table, for each reseller the CONTAINS function attempts to find a match for the combination of the reseller key and the employee key. Notice the user of the RELATEDTABLE function to pass the current reseller. The net effect is that the CONTAINS function returns TRUE if there is a row in the SecurityResellerFilter table that matches the ResellerKey and EmployeeKey combination.

You can use the Analyze in Excel feature (click the Analyze in Excel button in the Analysis Services toolbar) to test the security changes. To verify that the user has access only to resellers the user is authorized to see, add the ResellerName field from the Reseller table in the Column Labels zone.

092811_1402_BISMTabular3

 

The report should show on columns only the resellers that are associated with the interactive user in the SecurityResellerFilter table.

SQL Server DENALI CTP3 Demo VHD Available

Microsoft just released a Hyper-V image of SQL Server Denali CTP3 in action, including fully configured services and integration with SharePoint 2010 and Office 2010. The following software is configured on the virtual machine:

• SQL Server “Denali” CTP3

• SharePoint 2010

• Office 2010

CTP3 Issues with PowerPivot Management Dashboard

I ran into a couple of snags with the PowerPivot Management Dashboard and CTP3 of Denali. In this case, I’ve installed PowerPivot for SharePoint on a standalone domain controller.

  1. The Actions web part of the PowerPivot Management Dashboard refuses to load with the following exception logged in the SharePoint URL log ” Unable to acquire information about the NT group ‘Enterprise Admins'”
    Resolution: This is a known issue that will be fixed in RTM. The workaround is to (1) delete any accounts that display their SIDs, i.e. they were part of a domain that is no longer valid, and (2) to delete any universal or enterprise groups
    Connect item: https://connect.microsoft.com/SQLServer/feedback/details/685945/system-exception-unable-to-acquire-information-about-the-nt-group-enterprise-admins
  2. The dashboard doesn’t display any data although usage data is collected. This is caused by the fact that the PowerPivot system account doesn’t have rights to the PowerPivot SQL database and the timer job fails. The SQL Profiler shows the following error “The SELECT permission was denied on the object ‘vHealth’, database ‘DefaultPowerPivotServiceApplicationDB’, schema ‘Usage’. An error occurred while processing the ‘Health’ table.”
    Resolution: Grant the PowerPivot system account rights to all tables and views in the PowerPivot database
    Connect item: https://connect.microsoft.com/SQLServer/feedback/details/685955/no-data-in-powerpivot-management-dashboard-due-to-insufficient-rights

     

Reporting Services "Denali" Brings Export to Excel 2007

One welcome improvement in the forthcoming “Denali” release of Reporting Services is exporting to Excel 2007 (*.xlsx) file formats. I bet many companies will upgrade just to get this feature. In fact, the default rendering option when you export to Excel is to xlsx. The old Excel 2003 renderer is still there but it’s hidden by default. And, yes, BI projects now integrate with Visual Studio 2010!

For more details about Excel 2007 rendering, please read the Exporting to Microsoft Excel (Report Builder 3.0 and SSRS) topic in BOL.

082011_1609_ReportingSe1

082011_1609_ReportingSe2

Reporting Services SharePoint Integration in SQL Server Denali

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:

  1. Run the SQL Server Denali CTP3 setup and Install Reporting Services and the add-in on the SharePoint application server.081411_2142_ReportingSe1
  2. 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.081411_2142_ReportingSe2
  3. In the page that follows, configure the service application details as per your specific environment and click OK.081411_2142_ReportingSe3
  4. As with the previous releases, one more step is required to enable the SSRS content types in a given document library.

    a) Go to the document library settings page and click Advanced Settings. In the Advanced Settings page, select Yes for the Allow management of content types?

    081411_2142_ReportingSe4

    b) Back to the document library settings page, click the Add from Existing Site Content Types link.
    081411_2142_ReportingSe5
    c) In the Add Content Types page, add BISM Connection File (assuming PowerPivot for SharePoint is configured), and the Report Builder content types.

    081411_2142_ReportingSe6

Voila! Once this is done, you can create Crescent reports. You just need to create a BISM Connection File that points to a PowerPivot model or BISM Tabular model.

081411_2142_ReportingSe7