Now we know how to find all the calculation scripts in a cube by querying the DMV $SYSTEM.MDSCHEMA_MEASURES.
What about all those calculated sets in the cube?
In the Adventure Works cube, there are quite many calculated sets scattered around in the Sets folder… Read more
Calculated measures are visually identifiable
If you have access to a cube, it’s quite easy to find all the calculated measures.
The following is a screenshot from SSMS MDX query editor. The icon next to each measure can visually tell you if it’s a regular measure or a calculated measure.… Read more
Finding out MDX calculation scripts is a common task
A co-worker recently asked me what the calculation for a calculated measure is in our reporting cube.
If you have the Analysis Services project in Visual Studio locally, it is easy to find what the calculation script is from the Calculations… Read more
In SQL, NULL means undefined value
Any SQL programmer can tell you that zero is not NULL, NULL is not zero, and NULL is not even equal to NULL either, because NULL means undefined. One undefined value cannot be equal to another undefined value.
In MDX, NULL means an empty…
Drill-down report is a common design
In SSRS, it’s a common design to allow users to drill down to more granular data in a same report.
In the following snapshot, users will see a summary line that shows the Call Handled 130. The two days, 11/4 and 11/5 will not… Read more
Expert Cube Development with SSAS 2012 Multidimensional Models was published earlier this year by Packt Publishing. It’s the second edition of the very successful book on SSAS cube development by three well-known industry leaders, Chris Webb, Alberto Ferrari and Marco Russo.
This book is not a tutorial book on using… Read more
Table of contents
|Need to remove employees who have zero sales amount|
|Create a calculated measure with the IIF() function|
|The NON EMPTY keyword will take care of the removal|
|Ordering results with both numeric and string expression|
|IIF() statement VS. Scope() statement in MDX Cookbook 2012|
Need to remove…
Table of contents
|IsNumeric() funciton is too forgiven|
|Use Pattern Matching with LIKE keyword|
|LIKE keyword in T-SQL doesn’t support regular expression|
|Replace white spaces with empty characters|
IsNumeric() funciton is too forgiven
One of my SSIS packages failed recently with the following error message.
“…. failed with the following… Read more
Some SSRS reports have a large number of parameters
I recently had a chance to help a co-worker to modify an existing SSRS+Cube report. The first thing that caught my eye is the large number of parameters in the report.
Although I never really developed any reports that have more… Read more
OK, first thing first. We all know the famous Adventure Works multidimensional sample project. To learn the tubular mode/DAX, we also have a sample tabular project.
Download sample database and projects from CodePlex
You can follow this link to CodePlex to download the following files: tinyurl/AdventureWorks2012.
- AdventureWorks DW Tabular…
Keyboard shortcut Alt + F1 works now
SSMS has a rich set of keyboard shortcuts that works by default. But in SSMS 2008 I had one complain about the default Alt+F1 shortcut. The Alt+F1 shortcut is assigned to sp_help by default. It worked for tables, sort of. If you are… Read more
I recently attended a virtual training of Tabular and Power Pivot for Developers taught by Kathy Vick at Pragmatic Works. Kathy had a comment (also an inside joke for developers I think) that for those who already have multidimensional cube and MDX skills, learning about the tabular (and PowerPivot,… Read more
Thanks to Chris Webb’s comment (see the comment section). I am making a correction on this blog.
The title of this blog contains a few important words, “from the same dimension”. You might have guessed the opposite side of it is hierarchies “from different dimensions”.
But my correction… Read more
In Chapter 1 of our book, MDX with SSAS 2012, we’ve devoted a section “Setting a default member of a hierarchy in the MDX script” to show three places where you can set up Default members for hierarchies.
For more information about how to define a default member for… Read more
While you are writing and testing MDX queries in Microsoft SQL Server management Studio (SSMS), how many times you find yourself need to open the Visual Studio project in SQL Server Data Tools (SSDT) just to find out the dimensionality of a measure group, or the calculation formula for a… Read more
In Adventure Works cube 2008 or 2012, in the Dimension Usage tab, you will see that many intercepts of measure groups and dimensions are blank. To a specific measure group, the dimensions that have blank intercepts are un-related to the measure group. As a matter of fact you will see… Read more
Using variables in SSIS packages can make your ETL process more dynamic and respond to different scenarios during runtime.
Not only we can use variables, we also have many different ways to set values for our variables. This is all good news for developers. But like many things in real… Read more
The book’s link is here, SQL Server 2012 Reporting Services Blueprints.
This book is a step-by-step, task-driven tutorial that goes straight to the practical development of reporting skills, explaining actions as they are taken. If you perform the role of report development using SSRS in your job and you… Read more
The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.
Here’s an excerpt:
The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 120,000 times in 2013. If it were an exhibit at the Louvre Museum, it would take about 5 days for…
In this blog, I’d like to give special thanks to the people below who have taken time to write reviews for my book MDX with SSAS 2012 Cookbook.
“It helped me a lot in my projects and helped me advanced my MDX skill in a very short time.”