Dynamically totaling child members
Putting the dynamic totals on top or at the bottom can be a trivial or a big decision. Very often, visual tools reply on MDX queries to get the aggregation correct in the MDX query so that the need to aggregate in the visual tool is… Read more
After I posted this blog MDX Cookbook is a popular book, I received another email from my MDX book publisher.
Hope all is well with you.
Considering the enormous response we received for the last promotional campaign that ended on 12th March, would you be willing to…
We use NON EMPTY key word on the rows axis to remove rows with NULL values. It works only when all the measures are NULL. If any one of the measures has value, NON EMPTY key word will not be able to remove rows.
NON EMPTY on rows axis worked…
You might have seen error message like this from a SQL job that pulls data from an OLAP cube using OPENQUERY:
The OLE DB provider “MSOLAP” for linked server “CUBE_LINK” indicates that either the object has no columns or the current user does not have permissions on that object.
When… Read more
I cannot believe that I have not posted any blog since May 2015! It’s been a busy few months for my co-workers and I, working on an OLAP re-design project. From the re-design of the ETL processes, to the multi-dimensional model, to building the dimensions, measure groups and cube, to… Read more
There are many great tips in MDX with SSAS 2012 Cookbook
The book MDX with SSAS 2012 Cookbook has many great tips for MDX script writers.
Here are two that are from Chapter 5 Navigation:
- detecting a particular member by comparing object with keyword IS is better than comparing name
Why a seemingly innocent WHERE clause can cause large amount of data missing
In the previous post, ETL #71–Your data can mysteriously disappear after a WHERE clause was added (1), I talked about how I discovered a WHERE clause was added to a main procedure that processes the raw… Read more
Validations at end of ETL indicate missing data
At the end of each ETL job, I always run some sort of validation process to give me an indication that the data loading and processing are as expected or things have gone terribly wrong. The latter happened last Thursday when I… Read more
The secrete SQLFile.sql
We are all so used to clicking on the New Query icon (or the keyboard shortcut Ctrl+N). We know it will open a new query window in SSMS, an empty query window that is, but many of us never knew that something else was designed to happen… Read more
What can be done in SAS must be possible in SQL
A friend of mine is a data professional in marketing data analysis with expertise in SAS and pretty good working knowledge of SQL. When he sent me questions about “how to do in SQL…”, I often sent back a… Read more
If learning SQL is in your New Year Resolution, then you will be happy to read this post.
We all make resolutions that we fail to keep. What I am suggesting here will give that failure minimum chance.
Here is what I am suggesting:
- Get a copy of the SQL…
It’s hard to believe that it’s been 5 years since my first ever post, in which I simply spelled Business Intelligence. Thanks to everyone for being with me since then!
I blogged around my daily experience as a Data Warehouse and Business Intelligence developer. Most of my posts are about… Read more
ROLLUP, CUBE, and GROUPING SETS Operators have been around since SQL Server 2005. These operators are extensions of the GROUP BY clause.
The GROUPING SETS operator might be introduced later in SQL Server 2008.
I only have experience with the ROLLUP operator. My test of GROUPING SET also suggested that… Read more
When we design a database, we usually need to do an estimate on the size of the database based on the dimension and fact tables.
Keep the following information handy next time when you need to do so.
The lists are based on this article:
OLEDB is the native client for SQL Server. Every SSIS developer is familiar with how to use it. Almost every task in Integration Services can use an OLEDB connection. As a matter of fact, it’s a preferable client to use if your backend is SQL Server.
However, there is one… Read more
The Analysis Services Performance Guide from bunch of Microsoft Analysis Services experts have been updated since 2005 edition for 2008 R2 and for 2012.
- Microsoft SQL Server 2005 Analysis Services Performance Guide
- Microsoft SQL Server 2008 R2 Analysis Services Performance Guide
- Microsoft SQL Server Analysis Services Multidimensional Performance and Operations…
MSOLAP is a native OLE DB provider for Analysis Services
A common task during ETL is to process the OLAP dimensions and the cube after all the dimension and fact tables are loaded into the data warehouse. Integration Services provide two special control flow tasks for this purpose, the Analysis… Read more
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