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.

SQL #58–Where are my own templates in SQL Server Management Studio 2012

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

2 comments, 5,421 reads

Posted in Sherry Li's BI Corner on 27 January 2015

SQL #57–A one-liner query with PARTITION BY

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

4 comments, 6,280 reads

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

Learning SQL #1–Start from buying a Copy of SQL Server Developer Edition

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…

Read more

1 comments, 295 reads

Posted in Sherry Li's BI Corner on 9 January 2015

5th Blogging Year

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

0 comments, 191 reads

Posted in Sherry Li's BI Corner on 31 December 2014

SQL #56–Aggregation with ROLLUP operator is not fully supported

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

0 comments, 5,096 reads

Posted in Sherry Li's BI Corner on 31 December 2014

SQL #55 – Data Types and Storage Reference

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:

SQL Server 2012 Data Types Read more

0 comments, 205 reads

Posted in Sherry Li's BI Corner on 19 December 2014

SSIS #118–Is it an OLEDB or an SMO Server Connection?

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

2 comments, 5,367 reads

Posted in Sherry Li's BI Corner on 17 December 2014

SSAS #39–Building Optimal and Well-Tuned Dimensions

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…

Read more

0 comments, 371 reads

Posted in Sherry Li's BI Corner on 14 December 2014

SSIS #117–What is the Location in the MSOLAP Connection String

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

0 comments, 204 reads

Posted in Sherry Li's BI Corner on 13 December 2014

MDX #45–Find all the Calculated Sets and Their Calculation Scripts

Now we know how to find all the calculation scripts in a cube by querying the DMV $SYSTEM.MDSCHEMA_MEASURES.

MDX #43–Find a MDX Calculation Script

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

0 comments, 5,194 reads

Posted in Sherry Li's BI Corner on 14 November 2014

MDX #44–How to find all the calculated measures

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

0 comments, 252 reads

Posted in Sherry Li's BI Corner on 13 November 2014

MDX #43–Find a MDX Calculation Script

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

0 comments, 281 reads

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

MDX #42–IsEmpty? or = 0 ? or IS NULL?

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…

Read more

2 comments, 6,039 reads

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

SSRS #75 – Use RunningValue() Function and VB Script to capture discrete dates

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

0 comments, 6,327 reads

Posted in Sherry Li's BI Corner on 11 November 2014

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, 285 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, 340 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, 334 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, 377 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, 3,222 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, 1,089 reads

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

Older posts