Erik Veerman's Blog, page 6

String Replacement Puzzle

Courtesy of SQL Server Pro. You can find the original a […]

Some Thoughts About Microsoft’s Cloud Platform Roadmap

There have been a lot of changes in and around Microsof […]

Power BI Workshop Videos

I’ve created a series of five tutorial videos for a set […]

New Power BI Preview and Pricing

Some exciting announcements were made today about the new Power BI cloud-based business analytics service. Today Power BI is an add-on service for Office 365 that requires an enterprise-level license – about $50 per user per month

Windows 10

Windows 10 is moving along too. The new technical previ […]

How to resolve connection errors when loading data in SSAS Tabular project

This post actually applies to both multidimensional (cu […]

Power BI Semantic Model Supports Many-to-Many Relationships

Is is possible to duplicate the same many-to-many relat […]

Virtual Conference and other happenings...

Here's a quick update on happenings!  I have some great things coming down the pipe for me and also its been a busy 12 months!  But yes, blogging hasn't been my forte, but it's not for a lack of interest.  I hope to catch up a little in the next few months.

 

Looking to the future

Check out the SSWUG Business Intelligence Virtual Conference… http://www.vconferenceonline.com/business-intelligence/speakers.asp It looks to be a *great* conference and is September 24-26.  I'm presenting 3 sessions on Designing, Architecting, and Tuning your BI solution.  Don't miss out! Our new WROX SSIS 2008 Pro book is finished! (http://www.amazon.com/Professional-Microsoft-Server-Integration-Services/dp/0470247959/ref=sr_1_2?ie=UTF8&s=books&qid=1216041624&sr=8-2) Expect it to be on the shelves in a couple months.  We've added a lot of great stuff in there… I wrote the Data Warehouse ETL chapter plus there is a new chapter on leveraging the SQL 2008 RDBMS with SSIS that Grant Dickinson from MS authored. We (Solid Quality) has a new seminar series called SQL Directions… it's like a pre-conference seminars, deep-dive, lots of material without labs.  I am doing a SQL Server 2008 BI seminar in Atlanta from Oct 1-2, http://learning.solidq.com/na/CourseDetail.aspx?CourseScheduleId=302. It's a lot cheaper and less time than a class.  PASS this year looks to be very exciting, and they are expecting 3000 people!  I was accepted as a pre-conference speaker and will be presenting on Designing, planning, and Tuning a BI solution, http://summit2008.sqlpass.org/precon-erik-veerman.html I'm really looking forward to this and I have some great content to share about overall BI solution architecture. A new Training Kit is in process!  This is the SQL 2008 BI Implementation TK and we just started.  It's for the 70-448 Exam and we are building upon the TK 70 445 material… as the lead author, I've made a lot of decisions on the chapter content and layout to make this TK better for anyone planning on using SQL 2008 BI.  No links yet, but I'll keep you posted!

 

What I have been doing the last few months?

Well, for starters, my wife and I welcomed a new baby into the family in Feb (Caleb James Veerman).  He's our 4th kid! It's a little chaotic around the house, as you can imagine. I did a series of SQL Mag road-shows in May… on high-performance BI.  In fact, one week, I was in NYC on Monday, LA on Tuesday, Chicago on Wednesday, and Boston on Thursday.  Look for some future ones this fall! TK 70-445 was released last fall… http://www.amazon.com/MCTS-Self-Paced-Training-Exam-70-445/dp/0735623414/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1216041624&sr=8-1 I was the lead author for the book…  this one really stretched me and boy what a relief it was to get it out the door. Also, if you've read some of my earlier posts, I did some ground breaking work with the Many to Many relationships in SSAS and co-authored a White Paper with the SQL CAT team on how to optimize them.  Check out the paper here: http://sqlcat.com/whitepapers/archive/2008/05/03/analysis-services-many-to-many-dimensions-query-performance-optimization-techniques.aspx Wow, last fall was full of conferences… SQL PASS, SQL Connections, and a series of SQL Mag road-shows on Project REAL. Of course, project work and classes.  I've taught probably 8 classes in the last 8 months and have been working with a client in Atlanta implement SQL 2005 BI… they love it!  I always hear comments like "Wow, we've never had access to data like this before"

I've got some more things in the pipeline that I can't share yet, but stay tuned!

 

Erik

...on practical BI

Maximizing Attribute Relationship Aggregations in Changing Dimensions

I wrote an article for the Solid Quality Newsletter on understanding the impact of dimension changes on Analysis Services Attribute Relationships...   Read the full article here! http://www.solidq.com/na/TechArticleDetail.aspx?Id=11   BTW, if you want to sign up for our newsletter, you can sign up here: http://www.solidq.com/na/Newsletter.aspx   Erik

SSAS Many to Many Optimization Technique #2 (using partitions)

Yep, I am finally getting around to posting my *second* idea on optimizing many-to-many relationships.  Really, its more than an idea as I have used the technique successfully on a couple client projects.   Here's the summary, then I'll walk through how to implement this with Adventureworks...   Partition your intermediate MGs by date and include the date dim relationship in the dimension usage.   Again, the same goal is in play, which is how to reduce the number of records that are in the intermediate measure group.  In the prior post, I walked through the Matrix idea to actually compress the number of rows in the intermediate measure group.  This approach is a little different:   M2M queries use *ALL* the common dimensions between the intermediate measure group and the other measure groups needed when a M2M dimension relationship is used. The date dimension is the most common dimension used to filter or slice in MDX queries THEREFORE: If you add the Date/time dimension to the intermediate measure group AND partition by the date dimension, then when M2M queries are run that use the date dimension to filter/slice, the number of rows needed for the runtime join *can* be significantly reduced.   That's it!  And it works, I worked with a client where we had an intermediate measure group with 150M rows. Including this reduced queries times in some cases by 10-50X.  Here's the example for AdventureWorks and the proof.   Implementation Example:   1.) In DSV, right-click on Fact Sales Reason
replace table -> with new names query   SELECT FISR.SalesOrderNumber, FISR.SalesOrderLineNumber, FISR.SalesReasonKey,
FIS.OrderDateKey, FIS.DueDateKey, FIS.ShipDateKey
FROM dbo.FactInternetSalesReason FISR
INNER JOIN dbo.FactInternetSales FIS
ON FISR.SalesOrderNumber = FIS.SalesOrderNumber
AND FISR.SalesOrderLineNumber = FIS.SalesOrderLineNumber   save/close DSV   2) Go to Partitions and expand the "Sales Reasons" partitions rename Internet_Sales_Reasons to "Internet_Sales_Reasons_2001"   Change the source to be a query binding and use the following query:   SELECT FISR.SalesOrderNumber, FISR.SalesOrderLineNumber, FISR.SalesReasonKey,
FIS.OrderDateKey, FIS.DueDateKey, FIS.ShipDateKey
FROM dbo.FactInternetSalesReason FISR
INNER JOIN dbo.FactInternetSales FIS
ON FISR.SalesOrderNumber = FIS.SalesOrderNumber
AND FISR.SalesOrderLineNumber = FIS.SalesOrderLineNumber
WHERE FIS.OrderDateKey <= '184'   Create 3 new identical partitions with different SQL WHERE filters...   "Internet_Sales_Reasons_2002"
...WHERE FIS.OrderDateKey >= '185' AND FIS.OrderDateKey <= '549'   Internet_Sales_Reasons_2003
...WHERE FIS.OrderDateKey >= '550' AND FIS.OrderDateKey <= '914'   Internet_Sales_Reasons_2004
...WHERE FIS.OrderDateKey >= '915' AND FIS.OrderDateKey <= '1280'   Here's what your partitions will now look like:     3.) Go to the dimension usage tab and add relationships between the Sales Reasons measure group and create regular relationships to the Date, Ship Date, and Delivery Date dimensions.
Use the Time key... for the relationship.  Here's what it looks like:   Optional Step: Change the Internet Sales Detail to MOLAP.  The only thing this does is make it easier to see the, otherwise the M2M queries will submit SQL to the DB to get the result.  It still works both ways, but a little cleaner with MOLAP.   Here's the XMLA code of the project.   The Results
Now, turn on SQL Server Profiler and connect to SSAS.  Run the following query against the updated version of ADW...   SELECT [Measures].[Internet Order Quantity] ON COLUMNS
, [Sales Reason].[Sales Reasons].[Sales Reason] ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Quarter].&[2002]&[3]   Check out the trace.  Specifically look at the Partition entries for Sales Reasons partitions.   "Finished reading data from the 'Internet_Sales_Reasons_2002' partition."   Partitions 2003 and 2004 are not being scanned for the query... (NOTE: 2001 actually IS read and the only reason is because it only has 1454 rows, and SSAS will always read it)   Multiply this out to millions of records and dozens of partitions and your M2M queries that include a date filter or slice will perform way better.   The 2 keys to implementing this are: 1.) Include partitions in your M2M measure groups on Date ranges AND 2.) be sure to add measure group relationships to the date dimension(s) in the dimension usage.   That's it.   Regards, Erik