Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Sherry Li's BI Corner

Always wanting to publish novels, but having the fortune to work in the data warehouse corner of technology, Sherry Li started to write the mysteries of the Microsoft Business Intelligence. She writes everything from T-SQL to MDX, ETL to Expressions to Scripting, Reporting to Cubes. You can find her writings at bisherryli.wordpress.com.

MDX #35 – Properties() function with TYPED flag

 

Reader Query

My publisher recently forwarded me a question from a reader about the “Using the PROPERTIES() function to retrieve data from attribute relationships” section in Chapter 1 of the book “MDX with SSAS 2012 Cookbook”.

“The following code seems to always return true for [Is Numeric],… Read more

0 comments, 260 reads

Posted in Sherry Li's BI Corner on 27 December 2013

MDX+SSRS #34 – Query not returning results: there are good reasons

When we are learning MDX, we unavoidably have to deal with the tools we use. Adding confusions about the behaviors of the different tools to our still fragile understanding of the MDX language itself can create huge frustration for learners.

Here is one of them. In developing SSRS reports, we… Read more

0 comments, 638 reads

Posted in Sherry Li's BI Corner on 10 November 2013

MDX+SSRS #33 – Retrieve manager’s key and fully-qualified member name

A co-worker recently worked on a SSRS report and wanted to use a specific manager’s team for testing. The Manager is a filter for the SSRS report and can take a fully-qualified member name as the default value.

Using the Employee dimension in Adventure Works cube as an example, a… Read more

0 comments, 892 reads

Posted in Sherry Li's BI Corner on 8 November 2013

MDX #32 – Vincent Rainardi’s blog

I came across Vincent Rainardi’s blog, and thought I’d share it with the readers.

Sherry Li and Tomislav’s 2012 MDX Cook Book

I’d also like to thank Vincent for writing up the review so quickly.

I browsed through Vincent’s blog, and found that he authored this book:

Building a Data… Read more

0 comments, 346 reads

Posted in Sherry Li's BI Corner on 24 October 2013

SQL #52 – set statistics xml on

When tuning SQL queries, I find that I use the following three SET options quite often. 

set showplan_xml on

When showplan_xml option is ON for a session, the execution of SQL queries in the same session will only show the estimated execution plan. No query results will be generated.

Here… Read more

0 comments, 386 reads

Posted in Sherry Li's BI Corner on 24 October 2013

SQL #51 – Timeout when updating a big table

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

2 comments, 2,460 reads

Posted in Sherry Li's BI Corner on 24 October 2013

MDX+SSRS #31– Query Designer in SSRS only allows the Measures dimension in the first axis

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

0 comments, 1,796 reads

Posted in Sherry Li's BI Corner on 28 September 2013

MDX #30 – Get The Book: MDX with SSAS 2012 Cookbook

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

0 comments, 493 reads

Posted in Sherry Li's BI Corner on 22 September 2013

SQL #50–Deleting or updating data from a table of 20+ GB

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.

Although this article The Data Loading Performance Guide was written for… Read more

1 comments, 1,668 reads

Posted in Sherry Li's BI Corner on 22 September 2013

MDX #29–Co-authored MDX with SSAS 2012 Cookbook

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

2 comments, 341 reads

Posted in Sherry Li's BI Corner on 29 August 2013

MDX #28– Time functions will not work with subquery

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

0 comments, 605 reads

Posted in Sherry Li's BI Corner on 22 March 2013

MDX #27–Who are Ascendants?

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 checked the www.thefreedictionary.com, and… Read more

0 comments, 750 reads

Posted in Sherry Li's BI Corner on 2 March 2013

MDX #26 – SSN can only be referenced as a member property in MDX

I’ve blogged about the Properties()  function before.

MDX #11 – How to get number of cars each customer owned using Properties() function?

MDX #12 – Do not forget the TYPED flag in the Properties() function 

The Properties()  function is used to explore the attribute relationships in a dimension.

If… Read more

0 comments, 885 reads

Posted in Sherry Li's BI Corner on 21 February 2013

MDX #25 – Slicer or Sub-Cube?

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

1 comments, 1,316 reads

Posted in Sherry Li's BI Corner on 8 February 2013

MDX #24 – Leave out a dimension member using Except() Function

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

0 comments, 2,144 reads

Posted in Sherry Li's BI Corner on 17 January 2013

SSRS #73 – Use Calculated Field to Dynamically Set Default for Parameters

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

3 comments, 2,986 reads

Posted in Sherry Li's BI Corner on 4 January 2013

MDX #23 – “Hello World!” Lesson in MDX

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

0 comments, 466 reads

Posted in Sherry Li's BI Corner on 3 December 2012

MDX #22 – What to use for explicit sorting on dimensions

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

0 comments, 308 reads

Posted in Sherry Li's BI Corner on 3 December 2012

MDX #21 – Days in a month

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

0 comments, 255 reads

Posted in Sherry Li's BI Corner on 22 November 2012

MDX #20 – Last Data Date

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

1 comments, 248 reads

Posted in Sherry Li's BI Corner on 22 November 2012

Newer posts

Older posts