• Reporting Services Data Alerts

    October 30, 2011 / No Comments »

    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): Open the report in SharePoint. Expand the Actions menu and click New Data Alert. 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...

  • Partition Generator for Multidimensional Cubes

    October 24, 2011 / No Comments »

    Today happens to be my birthday. To reverse the tradition, how about I give you a gift? If you find yourself spending hours creating partitions in BIDS, you might find the Partition Generator sample (requires registration at the Prologika website) useful. Often, I partition larger cubes by month which may require creating quite a few partitions. Not only is this time-consuming and tedious, but is also error-prone as you can easily get the partition slice query wrong. Written as a C# console application, Partition Generator has the following design goals: Keep it simple on purpose and due to time constraints. So, no GUI yet and you need to have basic .NET coding skills. You will need Visual Studio 2008 or later with C# to configure it. Auto-generate a partition definition file in an offline mode without making changes to the server by default. You can use this file to replace...

  • Book Review: MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

    October 20, 2011 / No Comments »

    MDX is the query language for multidimensional cubes. Many BI practitioners perceive MDX to have a high learning curve probably not that much for the language itself but for the multidimensional concepts you need to master before you can get something out of it. When interacting with the community, I am frequently asked to recommend a MDX book. So far, my recommendations have been Microsoft SQL Server 2008 MDX Step by Step by Brian Smith at el for beginners and MDX Solutions by George Spofford at el for more advanced users. I will add MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli (MVP – SQL Server) to my recommendation list. This book takes an interesting approach that will be particularly appealing to readers who have already mastered the basics. As its name suggests, the book is a collection of recipes that you can quickly refer...

  • Disabling Foreign Key Constraints

    October 19, 2011 / No Comments »

    Disabling check constraints, such as foreign key constraints, is often required when populating a data warehouse. For example, you might want to disable a check constraint to speed up loading of a fact table. How do we disable check constraints in SQL Server? You can script each check constraint or you can use the undocumented sp_MSforeachtable function. ALTER TABLE [dbo].[FACT_ITEM_INVENTORY] NOCHECK CONSTRAINT [FK_DIM_DATE_FACT_ITEM_INVENTORY_Date_Id] -- disables a specific contraint sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' -- disables all contraints in the database Once the foreign keys are disabled, you can run the ETL job or delete records from the referenced table without referential integrity checks. Note that you cannot truncate a table even if you have disabled the constraints due to the different way SQL Server logs the truncate operation. You must drop the constraints if you want to truncate a table. You can use sp_MSforeachtable to drop constraints as...

  • Power View Automatic Metadata Filtering

    October 19, 2011 / No Comments »

    Similar to Report Builder models (now obsolete), Power View (formerly known as Crescent) filters metadata automatically. The reasoning behind this feature is that the tool should guide the user to choose only tables that are related to the ones used on the report to avoid Cartesian joins. For example, if the user has selected Sales Amount from the Reseller Sales table, Power Pivot automatically disables Currency and Geography tables because they are not related to the Reseller Sales table. The Metadata pane shows these tables greyed out and the user cannot choose a column from these tables. As useful as this feature is, it can get in the way for the same reasons the Report Builder metadata filtering didn't work so well with multidimensional cubes. Marco Russo has already reported an issue that this feature ignores M2M calculations in the model. I believe this will be even a bigger issue...

  • BISM Tabular Dynamic Data Security Over Bridge Table

    September 28, 2011 / No Comments »

    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. Given this...

  • Rendering Reports in Chrome and Safari

    September 27, 2011 / No Comments »

    A client complained that Reporting Services ReportViewer doesn't render reports in Google Chrome or Apple Safari browsers. The magic property to apparently fix this issue was SizeToReportContent="True" <rsweb:ReportViewer ID="reportViewer" runat="server" Height="100%" Width="100%" SizeToReportContent="True"> </rsweb:ReportViewer> Here is a link to Microsoft's official stand on the browser support for Reporting Services. A couple of notes: Currently Microsoft doesn't support report rendering in Chrome but are re-visiting the browser support matrix periodically. Report Manager doesn't work in Safari or Firefox. Report Manager supports IE only. However, report rendering by URL or ReportViewer should work with the above change.

  • Specifying a Default Report Name on Export with ReportViewer

    September 27, 2011 / No Comments »

    Normally, when you export a report, the Save As dialog box will default to the report name. However, if you the SSRS LoadReportDefinition API and view the report in the ReportViewer, you'll find that when you export the report it will default the name to a system generated name, such as Report(N).pdf. That's because LoadReportDefinition creates a transient report on the server that doesn't have a name. However, the ReportViewer LocalReport and ServerReport objects support a DisplayName property that can be used to specify a custom report name, such as: reportViewer.ServerReport.DisplayName = "My Report"; // the following lines are for reference only reportViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote; reportViewer.ServerReport.ReportServerUrl = new Uri(ConfigurationSettings.AppSettings[Shared.CONFIG_RS_ENDPOINT]);

  • SQL Server DENALI CTP3 Demo VHD Available

    September 8, 2011 / No Comments »

    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

    August 29, 2011 / No Comments »

    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. 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 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...

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.

Syndication