• ProcessUpdate and Partition Scans

    February 7, 2012 / No Comments »

    I'm working on minimizing the cube processing for a cube with some two billion rows in a fact table. I put together an ETL package that processes the dimensions with ProcessUpdate following by processing the latest partitions. I've noticed that processing one of the dimensions , which happens to be the largest (some 1 million rows) and most complicated dimension, resulted in partitions scans. The SQL Profiler showed the scans with the following events: Finished processing the '<partition name>' partition These partition scans don't result in SQL queries to the database and normally should execute pretty fast. In this case, however, the scans were taking plenty of time resulting in some 15 minutes delay in the incremental processing flow. With some help from T.K. Anand, it turned out that I had a design issue with that dimension. I discovered the issue by changing the KeyDuplicate error in the dimension ErrorConfiguration...

  • Record Attendance for Atlanta BI Group Last Night

    February 1, 2012 / No Comments »

    We had the pleasure to have some 70 people attending our January 30th, 2012 meeting of the Atlanta BI group. Our sponsor, Matrix Resources, was kind enough to give us the auditorium. FisionIO sponsored the meeting. Phil Per-Lee did us a "Prototypes with Pizza" presentation, titled Connecting the Dots. And, Carlos Rodrigues rocked the stage with the main presentation about dimensional modeling. I've uploaded pictures to the Photo Gallery section of our website and the slides to the Resources section. We've got some cool presentations lined up for next few months. Check our Calendar section to see what's coming.

  • Downloading Multiple Files from SharePoint using WebDav

    January 19, 2012 / No Comments »

    I had to download many ASP.NET pages from the SharePoint Pages library that were used to wrap up the Reporting Services ReportViewer webpart. I wanted the pages as files, so I could deploy them to another SharePoint server. Unfortunately, SharePoint doesn't support selecting and downloading multiple files. But, it does support the WebDAV protocol. Open Windows Explorer and type in the following URL: \\<SharePoint site>\<library> Example: \\elitex\Pages, where elitex is the SharePoint server and Pages is the document library. Windows Explorer shows all documents in the library. From there on, just copy the files you need to another folder. The only caveat is that you need to make sure the WebClient Windows service is running on your laptop. If you're trying to connect from Windows 2008 Server, then make sure the Desktop Experience role is installed as well, which installs that WebClient service.

  • Subscription and Alerts Issues with Analysis Services in SQL Server 2012

    January 17, 2012 / No Comments »

    As you probably now, Reporting Services doesn't allow you to create subscriptions with data sources that use Windows security because subsbscriptions are run in unattended mode. Moving to SQL Server 2012, we'll add Data Alerts to the list. This presents an issue if you use Analysis Services which only supports Windows security. The only option is to use Stored Credentials with the "Use as Windows credentials" checkbox checked. You won't able to pass the user identity by checking "Set execution context to this account". As with previous releases, "Set execution context to this account" works with the SQL Server data but it doesn't work with the Analysis Services provider. I raised this issue to Microsoft and I posted a bug report. The issue is under investigation but it's unlikely to get fixed before SQL Server 2012 ships. Please vote!

  • Online PowerPivot Class

    January 13, 2012 / No Comments »

    I am teaching my online Applied PowerPivot class for personal and team business intelligence on Jan 24th and 25th and there are still seats available.

  • Happy New Year 2012!

    December 31, 2011 / No Comments »

    As 2011 is winding down, it's time to reflect on the past and plan for the future. 2011 has been a very exciting year for Microsoft BI and me. Gartner positioned Microsoft as a leader in the 2011 Magic Quadrant for Business Intelligence Platforms. Although SQL Server 2012 will technically ship early next year, we can say it's a done deal as it's currently in a release candidate phase. The most important news from a BI perspective is the evolution of the Business Intelligence Semantic Model (BISM), which an umbrella name for both Multidimensional and Tabular models. The Tabular model provides us with a nice personal (PowerPivot for Excel)-team (PowerPivot for SharePoint)-organizational (Analysis Services Tabular) continuum on a single platform. Power View extends the BI reporting toolset with a sleek web-based reporting tool for authoring highly interactive and presentation-ready reports. In its second release, Master Data Services (MDS) comes out...

  • Using the Hash Group Hint to Speed up ColumnStore Indexes

    December 21, 2011 / No Comments »

    As I mentioned in my blog post on this subject, I've found a good use of SQL Server 2012 columnstore indexes to speed up significantly ETL processes that need to aggregate large datasets. But we run into a snag, which got promoted to a bug by Microsoft Support Services. Under some conditions, SQL Server would create a plan that uses a "stream aggregate" operator instead of the more efficient hash match aggregate. This is illustrated by the following plans. This plan uses the stream aggregate and the query is much slower. If you hover on the Sort predicate, you will see a warning that the sort will spill data to tempdb. By contrast, this plan uses the Hash Match predicate and the query is about three times faster. As I mentioned, this appears to be a bug with SQL Server 2012, which might not get fixed in RTM. Meanwhile, force...

  • Upgrade Issues with PerformancePoint 2010 Filters

    December 16, 2011 / No Comments »

    Issue: Migrate a PerformancePoint dashboard from MOSS 2007 to SharePoint 2010. We used the Import PerformancePoint Content feature to import existing dashboards from the PerformancePoint database and this saved us a lot of effort. However, filters failed with this rather obscure error: After tracing with SQL Profiler and looking at the Windows Event log, we saw the following almost as useless error): Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Query (1, 7) Parser: The syntax for '{' is incorrect. In this case, the dashboard is using a Time Intelligence filter mapped to an Analysis Services dimension. Resolution: The upgrade process has mapped the filter's formula in the connection to the scorecard. We fixed the issue by mapping the filter data source (not formula), as shown in the screenshot below. In this case, the CompareDay is the data source name.

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

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