I was just about to write a blog about the new features in the Release Candidate (RC) build of Power View and I saw that Robert had written a great blog already. It’s great to see the product coming along so nicely. Can’t wait to be able to use multidimensional cubes as data sources!
One feature that stirred a lot of excitement and deserves more attention is Power View export to PowerPoint. When you export a report to PowerPoint, initially you get static slides that show images of the report pages.
Don’t be fool however as you can click the Click to Interact button to render the report live. The interactive mode preserves all report interactive features. For example, in the screenshot below I am playing the scatter chart animation.
Behind the scenes, the interactive mode uses a Silverlight alternative hosting control (right-click the object on the slice and click View Code) that points to the deployed report on the SharePoint server. Therefore, a live connection and permissions to SharePoint are required to preview the report inside PowerPoint.
UPDATE 11/29/11
As explained in the SQL Server 2012 RC0 release notes, the pre-release bits of Silverlight 5 are not available in 64-bit. The RTM release will include a 64-bit installer and it looks like it be available pretty soon. Meanwhile, trying to interact with a Power View report exported to PowerPoint 64-bit wil fail with the error “Some controls on this presentation can’t be activated. They might not be registered on this computer.”
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2011-11-23 01:29:002021-02-16 03:48:41What’s New in Power View RC0
The public release of Microsoft SQL Server 2012 RC0 is now available.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2011-11-18 13:06:262021-02-16 03:48:38SQL Server 2012 RC 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
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2011-11-17 19:12:002021-02-16 03:48:36PowerPivot for Excel SQL Server 2012 RC0 is Here
Well, 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:
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:
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:
Remote to the SharePoint server. You must have SharePoint Farm Administrator rights to run the script.
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).
Copy and paste the script, and hit Enter to run it. The figure below shows the script in Windows PowerShell ISE:
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:
Execute the following script to output the configuration of all extensions: $apps = Get-SPRSServiceApplication
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.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2011-11-16 01:19:002021-02-16 03:48:23Report Server Settings in SQL Server 2012 SharePoint Integration Mode
MDXParameter is a nice little utility that captures MDX queries sent from SSRS reports and replaces parameter placeholders with the actual values. I’ve been using the excellent Darren Gosbell’s Query Capture sample which is one of the first utilities I install when starting a new project. MDXParameter has a few more features that you might find appealing, including saving the queries in a database and showing the parameters separately. Installing MDXParameter is simple:
Download MDXParameter from Codeplex and unzip it.
MDX Parameter requires a SQL Server database to capture the queries. It could have been nice to include an option to write the queries to a file or just the screen but currently you must create an empty SQL Server database. You can name the database anything you want.
Double-click MDXParameter.exe to start it. You will be prompted to specify the connection details. Note that MDXParameter will automatically create the table schema.
Click OK to launch the main interface and click Start to start the trace.
Go to your SSRS report and run it. Make sure to delete the *rdl.data file in the SSRS project folder to avoid running the report with cached datasets.
Click Stop to stop the trace. You should see the all report MDX queries captured. Select a query to see its statement in the MDX tab. If the query has parameters, MDXParameter will replace them with the actual values (if the Replace Parameter checkbox is selected). This of course is the main reason why you need MDXParameter as it saves you a great deal of time if you want to execute the query with many parameters in SSMS. The tabParametersGrid shows you a grid with the actual parameter values. The Execute MDX button is currently not functioning.
Problem: How do we stop the maddening parameter refresh for reports that use SSAS cubes? As you’ve probably found, nothing seems to stop it and the Never Refresh option on the Advanced parameter properties is useless.
Solution: Use the OLE DB provider for Analysis Services for the parameter queries, as shown in the attached report.
To expand on my announcement about SQL Server 2012 editions, I welcome the new BI edition for the following reasons:
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.
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.
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.
[View:https://prologika.com/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/2308.AdventureWorks.zip:550:0]If you have experience with multidimensional cubes, you know that most cubes have a Date dimension grained at a day level. If the fact table has multiple date keys, you can join the Date dimension multiple times to the measure group so the Date dimension can play multiple roles (a role-playing dimension), such as OrderDate, ShipDate, and DueDate. If you use Excel pivot reports connected to the cube, to get support for dates you need to make the following changes as explained in Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables:
Set the Type property of the Date dimension to Time. This is also required for MDX time-related functions, such as YTD.
Set the ValueColumn column of the Date key to a column of date data type.
Once this is done, Excel “understands” the date semantics and treats dates as dates when you sort and filter on any field from the Date dimension. It also shows a nice menu for time calculations (relative dates).
But what if another dimension has a date attribute, such as the customer’s birth date? Unfortunately, Excel won’t recognize this field as of date type and it will treat as text. Not good, but these are the limitations of Excel OLAP pivot reports.
How does BISM Tabular change things as far handling dates? Starting with SQL Server 2012 and PowerPivot version 2, you can mark a table as a Date table. In the process of doing so, you need to specify a unique identifier. Once this is done, you get the same support for dates with Excel pivot reports because they are actually OLAP pivot reports that see BISM Tabular models as cubes. Specifically, Excel will treat only fields from the Date table as dates. Take for example the following model (attached in the blog):
The Orders table has three date fields (OrderDate, DueDate, and ShipDate). In SQL Server 2012, BISM Tabular and PowerPivot support multiple joins between tables, as Kasper de Jonge explains in his PowerPivot Denali: Working with multiple relationships between two tables blog. Well, kind of, because only one join can be active (the one in the solid line). The other dotted relationships can be used for DAX calculations, such as SalesAmountByDueDate, SalesAmountByShipDate, etc. In other words, BISM Tabular doesn’t support true role-playing tables and you won’t get automatically DueDate and ShipDate instances of the Date table. You can import the Date table multiple times but only one table can be marked as a Date table. However, Power View (code-named Crescent) is designed from ground up to support natively BISM Tabular models. Consequently, the Excel date limitations disappear, as shown in the table below.
Date Features
Excel
Power View
Date types
Only in Date table
All tables
Relative Dates
Only in Date table
N/A
Power View treats any Date field as a date. For example, this Power View report shows the OrderDate and TaxAmt fields from the Order table. The OrderDate field is of date data type. As you can see, I can filter on dates after a given date and Power View shows the correct results. Unfortunately, Power View doesn’t support relative dates as Excel does so you must define DAX time calculations.
In short, here are some recommendations to get the most of dates:
If you need to support multiple dates, leave the columns as a of date data type. Don’t use integer keys or smart keys, which are a common practice in data warehousing, to join to the Date table.
Have a Date table that joins to the date that is mostly commonly used so users can aggregate by quarters, years, etc. Make sure that the date columns in the other tables don’t include times because you won’t be able to join 10/19/2011 8:40 PM to 10/19/2011. If you need to have the same support for the other dates, reimport the Date table multiple times.
Consider creating inactive relationships for the other dates to be able to implement DAX calculations.
Use Power View for reporting. Besides better handling of dates, Power View will give you better performance with transactional reporting, as I explained in my Transactional Reporting with BISM Tabular blog.
BTW, speaking of dates and BISM Tabular, Boyan Penev (MVP SQL Server) has implemented a nice Date Stream feed on Azure Data Market. If you decide to consume the feed, you would notice that the DateKey column is returned as ISO8601 date (such as 2011-10-29T00:00:00. Unfortunately, Excel doesn’t recognize the ISO8601 format. You might think that you can around this predicament by creating a calculated column that removes the time portion but you can use a calculated column as a unique identifier in the Date table. As a workaround, save the feed as a file and strip out the time portion so you can set the data type to Date.
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 one data region – Tablix1. In addition, Reporting Services shows you the data that feeds the region based on the report parameters selected.
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).
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:
They are only available in SharePoint. Reporting Services in native mode doesn’t support data alerts.
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.
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.
Well, 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.
