A co-worker recently asked me why she would get timeout when updating a column in a big table. This column will be updated to 0 (zero) because a default value of 0 (zero) constrain is now added to the table.
The table has over 40 million records. Although I didn’t… Read more
In Chapter 1 of the book MDX with SSAS 2012 Cookbook, in the first recipe “Putting data on x and y axes”, I have given a simple example to show how easy it is to use the CROSSJOIN function to "combine" more than one hierarchy into the COLUMNS and… Read more
MDX with SSAS 2012 Cookbook
by Sherry Li and Tomislav Piasevoli
Packt Publishing 2013
The book is now available in both paperback and eBook format. You can order copies from Amazon, Barnes & Nobel, Packt Publishing. It is also available on Safari Books Online.
Hope you will find the book… Read more
I don’t have a definition of what is considered a large SQL table. I am pretty sure, however, that a table with 20+ GB data plus another 20+ GB for a few indices is not a small table anymore.
I have been absent from this blog for the last few months. For those who have noticed, I’d like to offer my apology and also provide an excuse. I hope that co-authoring a book is a good enough excuse.
For the last few months, I have been working on the… Read more
I had a post last month, MDX #25 – Slicer or Sub-Cube?, verifying that the query context did not change with the subquery.
This can pose problems if you want to use the Time-series functions in MDX. Two functions come into mind, YTD() and PeriodsToDate().
Both functions need to… Read more
Many function names in MDX are very family-friendly. Children, parent, ancestors, descendants, ascendants, are all terms we often use in telling family stories. Except ascendants. I never really understand what this word means until I ran into this word in MDX.
Ancestors VS. Ascendants
I’ve blogged about the Properties() function before.
The Properties() function is used to explore the attribute relationships in a dimension.
If… Read more
Slicer, Axes and Calculations Can All Filter Data in MDX
Every developer with SQL background knows how the WHERE clause works. You put some condition in the WHERE clause, such as TheDate = Yesterday (pseudo code), and it will only return data for yesterday.
Not so straightforward in MDX. We… Read more
Functions such as NonEmpty(), Filter() and Exists() must use a numeric expression
Many of the MDX functions, such as NonEmpty(), Filter() and Exists(), seem to be easy enough to use, but they present quite a challenge for people with T-SQL background. The common theme among them is that they all… Read more
Calculated Field has existed in Reporting Services since version 2005. SSRS 2005 did not have the most attractive user interface design, so this pretty useful feature stayed hidden from me until version 2008.
Even in SSRS 2008, I did not pay much attention to it until I started to write… Read more
Almost every tool we learned has some sort of “Hello World!” tutorial lesson.
So here comes the “Hello World!” lesson in MDX.
Putting “Hello World!” directly on the X axis doesn’t work
You would think this query would work, by putting “Hello World!” directly on the X axis.
Unfortunately it… Read more
I often think that I am lucky to work in a profession that I enjoy very much. It makes everything I do a fun thing to do. Majority of the fun comes from the fact that I have my hands in almost every aspect of building a data warehouse. From… Read more
ParallelPeriod() function is very useful when we need to present data side-by-side for two parallel periods
ParallelPeriod() function is often used when we need to present data side-by-side for two parallel periods, such as in the example below where daily data needs to be presented side-by-side for each day in… Read more
Need to check the last time when our ETL processes ran successfully
Data warehouse developers routinely query the last data date (the last date when we have fact data) to check the last time when our ETL processes ran successfully.
It’s not hard to do it in TSQL. It just… Read more
A reader asked me to share a C Sharp script I used in this post, SSIS #114 – How many ways can you do INSERT-UPDATE?.
This code snippet tried to determine if the original value of MyFirstColumn is different or not compared to the incoming data in the pipeline.… Read more
Continuing from MDX #18 – Easy to concatenate row values into column in MDX.
I have not accomplished the goal of concatenating the set of top resellers in each country.
Count() function can count the number of tuples in a set
In this query, I am adding a count… Read more
Having worked with SSAS for a while and done some reporting with MDX queries, I started to explore opportunities to write SSAS “stored procedures”. I stumbled on a few blocks along the way to even set up the development environment properly before I got chance to identify scenarios to justify… Read more
Concatenating row values (within groups) into column in SSRS and TSQL
Concatenating row values into column is a never ending topic for report developers. It should be a simple task. That is, if the rows are not within groups. If the rows you are about to concatenate are customers in… Read more