I’ll be presenting “Personal BI with PowerPivot v2” for the Atlanta BI Group on Monday, February 27th. Join us to learn when personal BI makes sense and why PowerPivot is the best tool on the market when it does. The focus will be on the new features of PowerPivot version 2 which launch officially in a week or so.
For more information and to register visit our Atlanta BI home page.
My new book, Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling), will start shipping in a week with all popular resellers, such as Amazon, B&N, etc. I’ve been working on it for a few months and I’m excited to have it done. While waiting to buy the book, check the following resources to get you started with PowerPivot and Tabular:
– A sample chapter – “Introducing Business Intelligence Semantic Model”
– Video demos – I recorded over three hours of video content for selected exercises in the book
An insightful tour that provides an authoritative yet independent view of this exciting technology, this guide introduces the Tabular side of the innovative Business Intelligence Semantic Model (BISM) that promotes rapid professional and self-service BI application development. Business analysts and power users will learn how to integrate data from multiple data sources, implement self-service BI applications with Excel, and deploy them to SharePoint. Business intelligence professionals and database administrators will discover how to build corporate solutions powered by BISM Tabular, delivering supreme performance with large data volumes, and how to implement a wide range of solutions for the entire spectrum of personal-team-organizational BI needs.
WHAT’S INSIDE:
Chapter 1: Introducing Business Intelligence Semantic Model
PART I: PERSONAL BI WITH POWERPIVOT FOR EXCEL
Chapter 2: Personal BI Basics
Chapter 3: Importing Data
Chapter 4: Refining the Model
Chapter 5: Analyzing Data
Chapter 6: Implementing Calculations
PART II: TEAM BI WITH POWERPIVOT FOR SHAREPOINT
Chapter 7: Team BI Basics
Chapter 8: SharePoint Insights
Chapter 9: Managing PowerPivot for SharePoint
PART III: ORGANIZATIONAL BI WITH ANALYSIS SERVICES TABULAR
Chapter 10: Organizational BI Basics
Chapter 11: Designing Storage and Security
Chapter 12: Managing Tabular Models
Enoy!
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 to True and fully processing the dimension. Dimension processing failed because of the attribute relationships implied a one-to-many relationship between two attributes but the data didn’t support it. Somewhere along the design cycle, I turned off KeyDuplicate probably to get around the same issue in order to process the dimension successfully.
So, the moral of this story is:
BTW, the problem with duplicate key errors is that the server can move members around and that would cause indexes and aggregations to require being rebuilt. For example, if you have the following members in the source data:
Attr1Key Attr2Key
55 32
55 35
The first time, the engine may store 32 as the related member. But the second time it sees the rows during ProcessUpdate, it would potentially choose 35 as the related member. And that’s going to cause indexes and aggregations to need rebuilding.
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.
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.
Example: \\elitex\Pages, where elitex is the SharePoint server and Pages is the document library.
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.
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!
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.
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.
Looking forward to 2012 and beyond, here is my top 5 BI wish list:
On the personal side of things, I’ve been fortunate to stay healthy and busy (very busy). The Atlanta BI group, which I am leading, has grown in size and we now enjoy having 40-50 people attending our monthly meetings. For the past few months, I’ve been working on my next book, Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling), which I expect to get published in March. And, my consulting business has been great!
I wish you a healthy and prosperous year! I hope to meet many of you in 2012. Meanwhile, you can find me online at the usual places: www.prologika.com | blog | linkedin | twitter.
Happy New Year!
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 your queries to use the HASH GROUP query hint to force SQL Server to use a hash match with columnstore indexes.
GROUP BY Organization_Id,
Item_Profile_Id,
…
OPTION (HASH GROUP)
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.
