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.

Book Review: Why You Should Read Expert Cube Development With SSAS 2012 Cover to Cover

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

0 comments, 182 reads

Posted in Sherry Li's BI Corner on 15 June 2014

MDX #41–Remove employees with less than $100,000 sales

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…

Read more

0 comments, 212 reads

Posted in Sherry Li's BI Corner on 30 May 2014

SQL #54–Casting numbers with white spaces as Integer will fail

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

0 comments, 206 reads

Posted in Sherry Li's BI Corner on 30 May 2014

SSRS #74 – 2008 Cascading bug when you have a middle parameter

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

2 comments, 210 reads

Posted in Sherry Li's BI Corner on 9 May 2014

DAX #2 – Installing AdventureWorks DW Tabular Model SQL Server 2012

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.

  • AdventureWorksDW2012_Data.mdf
  • AdventureWorks DW Tabular…

Read more

0 comments, 1,957 reads

Posted in Sherry Li's BI Corner on 30 April 2014

SQL #53 – Keyboard shortcut Alt + F1 works now in SSMS 2012

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

0 comments, 440 reads

Posted in Sherry Li's BI Corner on 30 April 2014

DAX #1 – Learning DAX

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

3 comments, 408 reads

Posted in Sherry Li's BI Corner on 25 April 2014

MDX # 40 – CROSS JOIN does not return Cartesian product of two hierarchies from the same dimension

Correction

4/25/2014

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

2 comments, 327 reads

Posted in Sherry Li's BI Corner on 15 April 2014

MDX # 39 – Find Default Measure using MDX Query

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

0 comments, 974 reads

Posted in Sherry Li's BI Corner on 15 April 2014

MDX+SSAS #38–CALL ASSP.DMV() to document cubes in SSAS 2005

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

0 comments, 256 reads

Posted in Sherry Li's BI Corner on 12 February 2014

MDX+SSAS #37–Ignore unrelated dimension or not

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

1 comments, 490 reads

Posted in Sherry Li's BI Corner on 7 February 2014

SSIS #116–What is the variable value at runtime?

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

0 comments, 1,155 reads

Posted in Sherry Li's BI Corner on 29 January 2014

SSRS #73 – Marlon Ribunal and Mickey Stuewe’s New Book: Reporting Services 2012 Blueprints

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

0 comments, 264 reads

Posted in Sherry Li's BI Corner on 24 January 2014

2013 in review

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…

Read more

0 comments, 163 reads

Posted in Sherry Li's BI Corner on 1 January 2014

MDX #36 – Many thanks to the reviewers

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.”

hxy0135… Read more

0 comments, 171 reads

Posted in Sherry Li's BI Corner on 1 January 2014

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, 238 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, 581 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, 857 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, 331 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, 363 reads

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

Older posts