• Columnstore Indexes To Speed ETL

    December 8, 2011 / No Comments »

    SQL Server 2012 introduces columnstore indexes. Using the same in-memory VertiPaq engine that powers PowerPivot and Analysis Services Tabular, columnstore indexes can speed up dramatically SQL queries that aggregate large datasets. For a great introduction to columnstore indexes, see the video presentation, "Columnstore Indexes Unveiled" by Eric Hanson. I personally don't see columnstore indexes as a replacement of Analysis Services because an analytical layer has much more to offer than just better performance. However, in a recent project we've found a great use of columnstore indexes to speed up ETL processes. Issue: Perform an initial load of a snapshot fact table for inventory analysis from another fact table with one billion rows. For each day, extract some 200 million rows from the source fact table and group these rows into a resulting set of about 300,000 rows to load the snapshot fact table for that day. The initial estimates indicated...

  • What’s New in Power View RC0

    November 23, 2011 / No Comments »

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

  • SQL Server 2012 RC is Here

    November 18, 2011 / No Comments »

    The public release of Microsoft SQL Server 2012 RC0 is now available.

  • PowerPivot for Excel SQL Server 2012 RC0 is Here

    November 17, 2011 / No Comments »

    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

    November 16, 2011 / No Comments »

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

  • MDXParameter Sample

    November 11, 2011 / No Comments »

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

  • Stopping SSRS Parameter Refresh

    November 8, 2011 / No Comments »

    [View:https://prologika.com/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/5736.Product-Sales.rdl:550:0] 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.

  • Business Intelligence Edition in SQL 2012

    November 4, 2011 / No Comments »

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

  • SQL Server 2012 Licensing and Editions

    November 3, 2011 / No Comments »

    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

  • Handling Dates in BISM Tabular

    October 30, 2011 / 2 Comments »

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

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