SQL Roundup – 03/16-2012

Howdy! I was flying last week, and for the first time i sat facing a flight attendant. You don’t actually sit face to face with anybody in a flight, because, you know all the seat rows are facing in only one direction. But attendants sit in the opposite direction. That is a pain. I watched them make hilarious expressions, they reminded me of a stand up comedy by Russell Peters. You should watch it, it’s a nice start to your Friday.

Are you ready for another SQL Roundup? Not quite, i know. But here it is anyway.

  • Some great view points were shared for this month’s T-SQL Tuesday. Should you be one Jack of All Trades, or Master of None? I’m not linking to the original post, I don’t want my blog to show up as a ping back there –  it’s against the spirit. But follow the below link for some healthy advice. (http://sqlblog.com/blogs/argenis_fernandez/archive/2012/03/05/t-sql-tuesday-028-jack-of-all-trades-master-of-none.aspx)
  • While we are taking advice from experts, also read this interview of Andy Leonard. Andy is full of inspiration, as always. He is, actually, one of the persons who helped me get started with this blog. I’m grateful to have received his help directly. (http://tinyurl.com/6n76a4o)
  • Let’s talk BI. You know Vertipaq, right? The term used to define the in memory engine for SSAS Tabular and PowerPivot. Well, don’t worry about it anymore. It get’s rebranded to xVelocity.
  • Sure! Even small things teach great things. Fun With Aggregates. Paul White.
  • Here are a few useful videos covering SQL Server 2012 on Channel 9.

Have a good Weekend!

Follow Me:

Advertisements

Distinct Count in Analysis Services

Business users are often interested in finding distinct counts in addition to other aggregations. They may want to know the number of distinct customers calling customer support, the number of distinct employees selling business, or the number of distinct products that are being sold. Note the difference between distinct count and the other aggregations usually modeled in OLAP cubes; think sum of sales, count of orders.

Requirement:

I’ll use an online forum website as an example. Assume *SiteX* has several users, and these users are allowed to post on forums daily. Given this, let’s suppose, we want to find the count of distinct users that are posting in forums on any given day.

Star Schema:

I have created a star schema with two dimension tables, DimUser to store all the users, and DimDate to store all the dates. Fact table FactForum records user activity on forums.

clip_image001

DimData and DimUser has five rows each. The following image shows the Fact data – all the posts were posted by three distinct users.

DateKey     UserKey     TotalPosts
———– ———– ———–
20120124    1           15
20111217    1           9
20110921    1           16
20120115    3           5
20111119    3           9
20111119    5           17

Cube:

Next, I’ve created a cube with dimensions and measure group from the star schema.

clip_image002

The following is a snapshot from the cube browser, showing the total posts sliced by user and date.

clip_image003

It’s a walk in the park thus far. ain’t it?

Distinct Count:

Next, I created a new measure from the measures pane, configured usage to count of rows, and source table to DimUser. This creates a measure group from a dimension, in other words, dimension DimUsers now acts as a dimension as well as a measure.

clip_image004

clip_image006

clip_image008

The above dimension usage shows that DimUsers measure group is related to DimUsers dimension. The relationship is a fact relationship.

Lastly, I added a many to many relationship between DimUsers and DimDate. Many to many requires an intermediary measure group which in this case is FactForums.

clip_image009

The following image from the cube browser shows total posts and number of distinct users posting over calendar date (dimension).

clip_image010

Conclusion:

This is one way of modeling distinct count in analysis services. I find this useful for several reasons, one of which is performance.

@SamuelVanga

Further Reading:

SSAS Distinct Count Optimization White Paper.

SQL Roundup – 03/09/2012

Welcome to another week of SQL Roundup. Just in case you missed, SQL Server 2012 was released this week and you can download the evaluation edition here.

Clever and great improvisation. Create tag cloud style reports using SSRS. (Jason Thomas).

Jen McCown summarizes the agony caused during the SQL Server Virtual Launch Event.

Use this decision matrix to choose from the different BI tools. (Melissa Coates).

Don’t miss out on an opportunity to receive free SQL Server training.

So, wassup with BISM? Simran Jindal clearly explains the meaning of BISM!

Cheers!

Follow Me:

SSRS NoRows – Display a custom message when there is no data

In this short post, I’ll discuss NoRowsMessage property. When there is no data for a data region, the message you specify for the NoRowsMessage property will displayed to the user instead of a blank report.

I created a report with a tablix and configured the NOROWS property from the tablix properties window.

image

As with any other properties, you can give a static text or use expressions. The text I input here will be displayed when there is no data.

image

 

Report Preview:

image

For the record, NO, I wouldn’t use the message seen in the above image for my users and you shouldn’t too. 🙂

@SamuelVanga.

Introducing SQL Roundup

Following in the foot steps of John Sansom (b|t), I’m going to post a roundup of links from other blogs every Friday. I’ll call this SQL Roundup. John calls his “Something for the Weekend“.

Each Roundup will have no more than 10 links. Trust me, I don’t want to ruin your Friday with too much information. Areas i hope to cover include: T-SQL, SQL Business Intelligence, Data Warehouse, Blogging, Professional Development. These links will mostly be new blog posts, but occasionally I’ll point to older ones too.

Here is the first round:

SQL Roundup for 03/02/2012

Review: I Done This Andy Warren reviews a product and concludes his post with a tip that all new bloggers will find useful.

How To Write an Interview Winning CV You rock when your CV rocks. John shares some excellent ideas on writing a CV.

So, what is the BI Semantic Model? Chris Webb walks you back to early SSAS days and compares UDM with BISM.

SSIS – Name Those Connections by Andy Leonard. Your DBA will give you a hug. Not sure if you would want it though!

Why Use SSAS Tabular? by Marco Russo. SSAS 2012 comes in tabular mode, along with the traditional Multi-Dimensional mode, Alright. But, why use it?

Watch out for some activity on Twitter with #sqlroundup hash tag.

Enjoy!

Follow Me: