Often times, you want to analyze sales using a year to date view as shown below: While you can do this in an MDX formula in SSRS, you might find this calculation valuable enough to store a server-side calculation. To do this, you'll need to pick what date dimension or role playing dimension you want to slice this by. After you know that, you can use an MDX calculation as shown below for this: Sum(YTD([Delivery Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]) This will show a r...
Read More
We're moving fast at the BI Developer Network (BIDN.com). In an effort to make sure you get your BI questions answered, we've installed a new twitter interface that if your question in our forums doesn't go answered after 2 days, it will escallated in a number of ways. We'll first feature your question in our newsletter and it will also be automatically be tweeted to http://www.twitter.com/bidn for any followers. Your question will be featured on twitter with a prefix of "BIDN User SOS" with you...
I've been working this week to automate the BIDN.com's twitter connectivity and to do so, decided to build a quick Twitter interface to communicate with BIDN. I needed a simple console application that could be called via a trigger or a SQL Agent Job (my actual method). The entire application winds up only being a 50 lines of code or so. Essentially from a command line, you pass in your twitter account, password, the message and optional URL. If you pass a URL in, it will be converted to TinyURL...
As ETL developers, we typically lump anything that's NULL or something we can't find an ETL lookup match on to -1 or "unknown". We would first of course have to create an Unknown record in each dimension table then during the ETL attempt an INNER JOIN into the dimension table through a Lookup Transform or through a classic INNER JOIN. If a match could not be found, then it's branded as unknown or -1. On a recent project, a new need for unknown arose which I've seen in the past. This hardened my ...
After promising a dozen people a dozen times that I'd do this, I finally posted my MDX examples that use during my sessions http://www.bidn.com/files/ . You'll need to have the AdventureWorks cube installed, which you can get from the CodePlex site here: http://www.codeplex.com/MSFTDBProdSamples . Once deployed, open Management Studio and open the MDX Example.mdx code. Each block of code is commented telling you why you're doing what you're doing. Make sure you go off script though and experimen...