Our BI team will go to Redmond to vist Microsoft BI lab, this is an official visit, we will talk some issues we face, and some improment advises for SSAS.
Here are the details from my director, I think many of you may face the same issue in the list.
Greetings,
Our friends at Microsoft have arranged for us to come to the BI/SQL lab in Redmond during the week of 02/25 and use the expertise of their top BI/SQL technical team to examine RSi software platform and solutions, and offer advice about our current technology pain points and future directions.
The official name of the Microsoft team is SQL Customer Advisory Team (SQLCAT), and their role is to assist customers to leverage Microsoft SQL platform capabilities to their fullest extent. SQLCAT members get involved in the largest, most complex SQL/BI deployments, including Yahoo’s 24Tb cube, Xbox’s 4Tb cube, and RSi’s 50Tb worth of data across 100s of cubes. As an added benefit, we had principal members of the SQL development team reviewing our architecture and answering our questions about SQL platform internals.
We have presented our main production pain points, and asked for relief. SQLCAT team was very willing and eager to help, and provided us with several incremental improvements, plus advice on how to work with Microsoft Tech Support to resolve a few additional bottlenecks. We will implement all relevant improvements shortly. The main takeaway from the meeting is that our solution is utilizing the capabilities of SQL server platform well, and our design patterns push the platform to its limits. In fact, there are several innovative approaches implemented by RSi that SQLCAT is considering to adopt as part of their arsenal.
Details are below (non-techies feel free to skip):
- Microsoft fiscal cliff: I talked about the financial impact of the core license model in SQL 2012 Enterprise edition. They promised to discuss if they can license us the cheaper BI edition, but enable the key features we need from the Enterprise edition. No guarantees, though.
- Microsoft suggested use of Column Index feature in SQL 2012 Enterprise Edition because it is much fasted for data warehouse queries. Unless we can resolve the item above, this is not currently an option.
- Microsoft suggested upgrade to the Windows Sever 2012 edition of the operating system, and it appeared to gain us about 5% of performance in their lab. I will work with the TechOps to understand the impact of an upgrade on our production environments.
- Microsoft recommended using SAN for our hosted environment. RSI TechOps are currently putting together a system, and we’ll need to benchmark.
- In the lab, we split the Stage SQL database and the Stage SSAS cube between two separate physical servers, and it improved performance by 15-20%. We can benchmark this configuration for our hosted environment.
- Speed of cube processing. There is a configurable optimization parameter that may gain us 5-10% of cube processing speed: we will benchmark and deploy. (Trace flag T1118).
- Multiple cube synchronization bottleneck. Currently, only one silo at a time can execute the last step of the synchronization process on a server instance, and each silo takes 1-2 minutes, so for 50 silos the queue wait becomes prohibitive. Microsoft suggested to use an alternate mechanism that should drop the delay to under 10 seconds per silo. Not perfect, but much better. The suspected bottleneck is our use of Trace database to collect queries we use for automation of aggregations. Microsoft suggested using another tool to do this (Profiler).
- Microsoft suggested using the in-memory cube model for speed. It may be an option for smaller cubes: we still need to solve the huge cube performance issues.
- We discussed scaling out of large cubes and creation of multi-retailer cubes by using the linked cube (virtual cube) approach. Not an option for us at this point: distinct count metrics do not work.
- Large cubes with millions of files appear to hang SSAS. Microsoft asked us to reproduce and submit a ticket.
- We use recursive MDX queries to extrapolate weekly distinct count metrics to daily level and these queries are slow. This is a known problem in the MDX query engine. The fix is TBD.
- SSAS server crashes with very cryptic error messages, and it is hard to debug. SQLCAT members commiserated with us about this: they have the same pain point. The fix is TBD.
- We asked to implement security for cube Perspectives to enable native attribute-level security. SQLCAT admitted that the idea is interesting.
- Microsoft demonstrated an unreleased version of the PowerView Sharepoint tool, which allows users to build interactive Web reports using SSAS cube data. The tool looks promising, and once it is fully released, we can review again.
- Microsoft talked to us about their Hadoop based offering. We may want to revisit a future release of this product.
