Vidas Matelis Analysis Services Blog

Yesterday I published Portfolio Slicer application that lets you track financial investments (stocks, ETFs, mutual funds) in Excel. I used Excel 2010 with PowerPivot 2012 to create PowerPivot database with over 90 DAX calculations and I could not be more happier with results. In the past I tried to use existing online services to track my investment accounts, but none of them would completely do what I wanted. So I decided to build investment tracking BI application using PowerPivot. Read the rest of this entry »

Today SQL Server 2012 was launched and Microsoft posted on their website set of videos that would help you to understand what is new or improved in this release. For my blog readers I would recommend watching at least following videos (all under “Breakthrough Insights” tab):

• “Rapid Data Exploration: Empowering End User Insight Through Rapid Data Exploration and Visualization with Power View” by Carolyn Chau, Lead Program Manager
• “Managed Self-Service BI: End User Created, IT Managed” by John Hancock, Principal Program Manager Lead
• “Credible, Consistent Data: Going Under the Hood with the New BI Semantic Model” by Ashvini Sharma, Principal Program Manager Lead
• “Big Data Analytics with Apache Hadoop and SQL Server 2012” by Saptak Sen, Senior Product Technical Manager
• “Breakthrough Insights with Microsoft Big Data” by Dave Campbell, Microsoft Technical Fellow

These are short presentations (about 20min each), you can watch them here.

Great news – Microsoft just announced SQL Server 2012 RTM.

You can already download SQL Server 2012 Data Mining Add-in for Microsoft Office 2010, SQL Server 2012 RTM Evaluation and Microsoft SQL Server 2012 PowerPivot for Excel 2010 . Other download will appear shortly.

Also, for more information make sure to attend SQL Server Virtual Launch event – should be very interesting.

I am especially excited for new SSAS Tabular release. For past few months I started to use DAX more and more and with every day I like it more – it is very powerful and flexible!

I already wrote about my experience in the SSAS maestro program and today I can share with you that I am one of the first 11 SSAS Maestros!  The official announcement on the first wave of SSAS Maestros was posted on the SQLCat.com website today. I am very happy to have received this award as it was a very challenging but worthwhile experience .

A big congratulations to all of the SSAS Maestro recipients: Ulf Ackerman, Alberto Ferrari, Greg Galloway, Darren Gosbell, Dirk Gubbels, Michael Horne, Marco Russo, Harlan Smith, Chris Webb and John Welch.

This blog post is about my experience in the SSAS Maestro program. As I am writing this post (May 1st, 2011), I still don’t know if I passed all of the necessary requirements to become a SSAS Maestro, and it will take a few more weeks or even months till the lab, exam and case study results will be available. There is a very good chance that I have not achieved the SSAS Maestro requirements, as this course and the tests were very difficult. However, I still want to share my experience, as I strongly believe that this was by far the best training on SSAS that I have ever participated in. Read the rest of this entry »

In a few weeks I am planning to go to Redmond for “SSAS Maestro” training. I am very excited that I was accepted for this training and I want to prepare for it as much as I can. As I was going through available SSAS papers, I realized that quick list of SSAS white papers could be useful for other SSAS developers, so I am posting it here:

• SQL Server 2008 White Paper: Analysis Services Performance Guide
• Analysis Services Processing Best Practices
• Analysis Services 2005 Processing Architecture
• Analysis Services Distinct Count Optimization
• Analysis Services Distinct Count Optimization Using Solid State Devices
• Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques
• SQL Server 2005 Analysis Services (SSAS) Server Properties
• Running Microsoft SQL Server 2008 Analysis Services on Windows Server 2008 vs. Windows Server 2003 and Memory Preallocation: Lessons Learned
• SQL Server Best Practices Article: Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services
• Analysis Services ROLAP for SQL Server Data Warehouses
• Scale-Out Querying for Analysis Services with Read-Only Databases
• Microsoft SQL Server 2008 Analysis Services Consolidation Best Practices

Few years ago on my blog I posted scripts “SSIS Package to drop/create partitions based on partition list in the SQL Server table” and “SSIS package that process all partitions/measure groups/cubes in one database“. These posts contained partial scripts that I developed for company “Insight Decision Solutions Inc.” to maintain partitions in the Microsoft SQL Server Analysis Services. This company sells, customizes and implements pre-packaged data warehouse solution (using SQL Server, SSAS, SSIS, SSRS, SharePoint and Excel 2007) for “Life” and “Health” insurance companies. Recently “Insight Decision Solutions Inc.” owners let me post full script on how to automate SSAS partition management . Here are step by step instructions that I adjusted and tested on Adventure Works database. Most of the code comes from my earlier published posts, here I just added information how everything works together.

Read more and download code here…

I recently posted a new PowerShell script to process all dimensions and cubes in the specified Analysis Services database. This script has a few additional parameters that give you more control for your SSAS DB processing. First of all, you can specify the MaxParallel processing parameter to control the level of parallelism. You can also specify how many processing commands you want to execute per batch.  Read the rest of this entry »