Analysis Services

Microsoft Analysis Services

The following is one of the scenarios that I showed during my “Drop your DAX” talk at SQL Saturday #296 in Melbourne. Currently SSAS Tabular and PowerPivot models can only have a relationship based on a single column. So what do you do when you need to join based on multiple columns? Ideally you would solve this during your ETL. With a type 2 slowly changing dimension you typically want to insert the surrogate key for the dimension into the fact table. As you may know, “type 2” dimensions can have ......
A few weeks ago I posted a way to manually find and kill long running SSAS queries. In this post I’ll take it a step further and show you an automated solution. The idea behind this technique is inspired by a blog post Chris Webb did some years ago. Chris implemented his solution using SSIS while this version uses PowerShell. You might ask - why create a Powershell version? Well it does a little bit more in that it logs the cancelled queries and sends the user an email. It also uses membership in ......
As Rob has just said, this weekend is going to be a big one for the Australian SQL Server community as we are having our second ever SQL Down Under code camp at Wagga Wagga (which is curiously pronounced "Wogga Wogga"). It looks like we will have all 7 Australian SQL Server MVP's at this event. Last year's event was great fun and it's shaping up to be a good one again this year. I'm heading up there tomorrow and looking forward to seeing some great presentations and catching up with friends that ......
Recently one of our production Tabular servers was being hit with a number of extremely large queries that were causing excessive load and impacting the performance for other users. These queries were pretty much attempts at extracting detail level information. Some of these were due to the fact that some of the users had their own alternate hierarchy which we had not been informed of, so we were able to extend the cube design to rectify this. Other users were trying to build 10+ page reports in ......
Sometimes when troubleshooting performance issues you may want to comment out the MDX Script in your cube in order to quickly isolate whether the MDX Script is a significant contributor to the issue. So if you can reproduce your issue in your dev environment you can open up your project in BIDS / SSDT, comment out the script and re-deploy. But what happens if you can't reproduce the issue against dev. You may not have the same server specs, or the same data volumes or you may even have design changes ......
I was experimenting with some of the PerformancePoint APIs today, but I was doing something wrong as I kept getting ERROR! back. I assumed that some of the parameters that I was using was resulting in invalid MDX being generated. I was running against a test database on SSAS 2005 and as I suspected there was a syntax error in the MDX. Unfortunately Profiler against SSAS 2005 showed me the error, but not the offending MDX, which was not much help. On a whim I decided to move my test database to SSAS ......
Does that title make sense? I don't know if it does, but I can't think of another description for this problem. If anyone can think of a better title I would love to hear it. It's hard to explain in words so let's jump into some code examples. Consider the following SQL statement against the AdventureWorksDW relational database. The requirement is to select a list of 4 cities and then want to see the order quantity grouped at the country level. SELECT g.EnglishCountryRegionName Country ,sum(OrderQuantity) ......
You have designed Aggregations for your cube, but how do you know that they are currently processed? Hopefully you have your processing routines setup in production so that your indexes are always kept processed. But maybe you are working in a development environment or you are performance tuning that you want to double check that your aggregations are currently processed. It is not immediately obvious how you can figure if the indexes for a partition or a set of partitions are processed as this ......
So usually when I want to test an MDX expression in SSMS I'll write a query with a "WITH MEMBER…" clause to create a query scope calculated measure. But sometimes you may want to test a scoped assignment before putting it in your cube script. The following steps show you how to do this. 1. Click on the button to open an new MDX window, enter your server name and then click on the "Options >>" button 2. You must then specify the database that you are using 3. Then under the "Additional Connection ......
I'm currently in Seattle enjoying the start of some of the activities around the SQL PASS Summit. One of the activities that I'm looking forward to is the book signing session for the MVP Deepdives Volume 2 at lunchtime on Wednesday. I was fortunate to be one of the 60 or so authors this time around with a chapter on using Powershell to manipulate Analysis Services databases. All of the proceeds from this book go to support Operation Smile. You'll find the book's website here: ......

Full Analysis Services Archive


About Me
I am a consultant, based in Melbourne Australia. I primarily work in the Business Intelligence area with SQL Server, although I also dabble in Content Management Server and .Net coding

Contact Me


Any and all code, software, examples, suggestions and anything else on this web site is available for you to use at your own risk. No warranty is expressed or implied.
Views and Opinions
The views and opinions expressed on this web site are not necessarily the views or opinions of my employer.

Subscribe in Bloglines Subscribe in NewsGator Online

Locations of visitors to this page


Tag Cloud

Article Categories


Post Categories

.Net Blogs

Aussie Bloggers

BI Blogs

CMS Blogs