Microsoft Business Intelligence

It's all about BI.

  • Award from Microsoft…

  • Page Hits

  • Hits Since Inception!

    • 4,097 hits

A useful short cut in SSMS…

Posted by Santosh Joshi on October 7, 2012

Just came across a simple yet useful feature of SSMS for creating short cut for commonly used queries. Let’s get started -

1. Open your SSMS and go to Tools -> Options -> Environment -> Keyboard.

2. Type Select * from in Ctrl+3 as shown in the below screen shot -

Image
3. Now close your SSMS and re – open it. Make sure ‘select * from’ is present there in Tools -> Options -> Environment -> Keyboard.

4. Now go to any database, drag any table and press ctrl+3. You will see all the records which you would have got by writing select * from <tablename>. Make sure the table is selected in the query pane before hitting ctrl+3 otherwise it will show you error like – “Incorrect syntax near ‘from’.’

This will save a lot of time of yours in your project where you need to write “Select * from” thousand times. You just can drag and drop all the tables where you want to do the analysis and execute them as per the need basis. You might also can create 2 short cuts one for “select * from” and another for “select top 10 * from “.  You can also use this concept for other commonly used commands like Truncate & Delete, but with utmost care, I personally would avoid them.

Posted in SSMS | Leave a Comment »

An interesting link…

Posted by Santosh Joshi on September 29, 2012

Today I thought to spend some time understanding processing architecture of SSAS engine. When I was reading about dimension processing I encountered a concept – bit map indexing, which are created by SSAS engine for faster retrieval at query time; I tried to give a thought but was not able to recall the concept, I thought to Google it out. In the search I found an interesting web site – http://iwillgetthatjobatgoogle.tumblr.com/post/19125290679/bitmap-index.

It’s name is self explanatory – “iwillgetthatjobatgoogle”. Although, I don’t have any keen desire to go there (actually it’s too difficult for me ;-)), but what I like in this site is, tit bits which revolves around computer science. So anyone who loves computer science would surely like this link. Moreover, the topics are quiet precise! :-)

Posted in SSAS | 2 Comments »

Working with dynamic connection in SSRS

Posted by Santosh Joshi on September 25, 2012

Scenario – I was working on a report which has several data sources (servers), each data source contains set of databases. When you select a server, corresponding database(s) shall populate in the second drop down of the report, well the scenario was slightly complicated than this but discussing that here is beyond the scope of this post.

Solution – It’s simple. Firstly, you can’t make dynamic connections in the shared data sources. If you are trying to make your connection string dynamic you shall use embedded data source and use below expression for dynamic connection string -

=“Data Source=” + Trim(Parameters!ServerName.Value) + “;Initial Catalog=” + Trim(Parameters!DBName.Value)

Moreover, you cannot test it in BIDS, rather you shall deploy your report and test your report at run time. I wasted some time today thinking that where I’m wrong and why it’s not working in BIDS but testing it in BIDS is not allowed.

Hope you will save some time after reading this. :-)

Posted in SSRS | 2 Comments »

Stepped Reports – An alternative of Drill Down Reports!

Posted by Santosh Joshi on August 8, 2012

Recently, I encountered a scenario where we need to show multi – level hierarchy in the rows of the matrix. Since the levels were like 5 or so, the overall design of the report wasn’t pretty!

Stepped report layout is an elegant way and you can also achieve drill down features through it.

Drill Down Reports:

First we will see how drill down reports looks like. I’ve used Adventure works cubes and created following drill down report with 4 levels.

You can notice that drill down report is not so elegant design when levels are more than 3 and it could be clumsy when you have big names of the level members.

Stepped reports:

Now we will see another way of showing the same data but in different form – Stepped report layout.

If you notice here we have put all the different levels into one column and by putting appropriate indentation in each level we have formed a stair case a.k.a. stepped layout. Isn’t it much better than drill down layout?

Let’s see how we can design it…

Step 1 – Create a basic data set using below MDX.

SELECT
[Measures].[Internet Sales Amount] ON 0
,[Geography].[Geography].ALLMEMBERS ON 1
FROM [Adventure Works];

Step 2 – Use Matrix report item and drag and drop columns as shown in below screen shot.

Step 3 – This is an important step. Right click on Postal_Code group -> Add Total -> Before. You will get below design layout, if you notice a new row with Total is added (encircled) -

Step 4 – In the cell Total replace it with City (1) and then remove City column (2) as shown in below screen shot -

Step 5 – Repeat steps 3 & 4 for all the groups from City to Country, you then will see your report design as below -

Step 6 – Now you need to give final touches to your report e.g. renaming column name, indenting or padding each levels, making drill down capability etc. and you are done!

Posted in SSRS | 2 Comments »

Two useful tips working with SSMS 2008

Posted by Santosh Joshi on March 16, 2012

Today I am going to discuss two useful features of SSMS, which not only helps you to avoid mistakes but also help in organizing your servers and doing multiserver queries. SQL Server 2012 is about to come and I am here quiet late blogging this feature of SQL Server 2008 but better late than never :)

So here is the tip #1 -

1. Windows Connection Coloring: I am sure many would have done this mistake like I do that is firing a query in that server which is not meant for it. Nothing bad in that, to err is human but sometime this may put you in an awkward situation. Colors are more informative than text that’s why danger sign is made up of “red” color :) So the basic idea is to color the server connection e.g. Green for Dev, Yellow for Test and Red for Prod. It really can prevent you running queries in Prod by quickly recognizing it with Red color. The technique is really simple -

(a) When you connect to any server using SSMS, just go to Connection Properties tab and select Use Custom Color: check box and choose appropriate color as shown below -

Now once you query to this server you will get screen like below -

You can see the Red color in the bottom most bar. Similarly you can have coloring for other connections.

Note – Please see comment section, nice tip by Shailesh, in case you want to move status bar at the top instead of bottom, which makes the editor more intuitive.

And now here is the Tip #2 -

2. Querying Multi Server: Sometime it’s really painful to check the result set of same table across different servers especially when you do migration of database and want to do a quick sanity check. So here we will see how we can achieve the result set in the same window.

In SSMS click View -> Registered Servers or type Ctl+Alt+G. Then right click Local Server Groups and click New Server Group…as shown in below screen shot -

You can create Server Group named “Project” and then register servers to that group say Dev by right clicking Server Group and clicking New Server Registration…

Similarly you can register other server say Test & Prod as shown below -

Once servers are registered you can click “Project” and fire query by clicking New Query as shown below, you can see result of all the servers into single screen.

Hope you find it interesting & can use it in your work.

Cheers!

Posted in SQL Server, SSMS | 9 Comments »

Microsoft Contributor Award 2012

Posted by Santosh Joshi on February 14, 2012

I was somehow not able to continue my blogging but today when I got MCC award from Microsoft I wasn’t able to refrain myself from sharing my experience especially for those who are aspiring for it.

MCC & BI certifications were my top priority for 2012, but I never thought Microsoft would be so lenient on me, thanks Microsoft, but awards in any form and timings are always nice, at least I think so! :-)

Well, I guess, why I got this award was because of my contribution in MSDN community although it was just very “little”. I secured around 2000+ points and answered over 100+ questions which is OK but nothing impressive. So it means it’s quiet doable and one who is Microsoft technology enthusiast should do it. It’s not just about this award but there are many other benefits which you get from it like first of all sense of satisfaction on helping others, who knows one is in what situation and your advice saved him/her from getting into awkward situation and then you also learn many new things and lastly you make your social network stronger!

One thing, I’d like to advice  MSDN folks is, while replying to others, we should be polite and humble and try to help them and make them really feel comfortable so that they not only get your expert advice but also in future can contribute to MSDN.

Needless to say, I feel really happy by getting this award, thank you so much Microsoft.

Cheers!

Posted in Miscellaneous | 16 Comments »

Creating KPI using Wingdings in SSRS

Posted by Santosh Joshi on December 1, 2011

Although, in SQL Server 2008 R2, Microsoft has introduced “Indicators” for KPI but we can also achieve using Microsoft fonts – “Wingdings” or others based on business need. To know more about various fonts, you can search for “Character Map” application in your system, in Vista, it looks like as below -

Let’s create a basic report, having a table and dataset using below query -

SELECT ‘A’ AS SalesPerson, 100 AS Sales, 110 AS Target
UNION ALL
SELECT ‘B’ AS SalesPerson, 200 AS Sales, 190 AS Target
UNION ALL
SELECT ‘C’ AS SalesPerson, 300 AS Sales, 300 AS Target
UNION ALL
SELECT ‘D’ AS SalesPerson, 400 AS Sales, 500 AS Target

Select any wingdings character from the Character Map application and copy it, so the character for the Up Arrow would be “á” and for down arrow would be “â” and for the slanted arrow would be “ã”. We will use all these characters in our report.

Following is the layout of the report -

First 3 columns are populated from the query itself, but KPI is the column which we need to configure so that we can see appropriate KPIs. Let’s make a small business logic here -

When Sales > Target, arrow should be Up and in Green color, if Sales >0.90 * Target it should be slanted and Yellow in color else down and Red in color. The logic taken here is simple, just for demonstration purpose.

Now, right click the KPI text box and click expression. In expression, copy and paste below code, which I feel is self explanatory -

=iif(Fields!Sales.Value>=Fields!Target.Value,”á”,iif(Fields!Sales.Value>0.90*Fields!Target.Value,”ã”,”â”))

Now, go to text box again and right click -> Textbox properties, select Font on left hand pane and select Wingdings and in the color expression put below code -

=iif(Fields!Sales.Value>=Fields!Target.Value,”Green”,iif(Fields!Sales.Value>0.90*Fields!Target.Value,”Yellow”,”Red”))

…and you are done! Here is how our table will look like when you preview it -

Cheers!

Posted in SSRS | 8 Comments »

Naming Convention – SSIS package development

Posted by Santosh Joshi on November 30, 2011

Here is one good link of naming convention for SSIS by Jamie Thompson. I used it recently in one of my development project in SSIS -

http://consultingblogs.emc.com/jamiethomson/archive/2006/01/05/SSIS_3A00_-Suggested-Best-Practices-and-naming-conventions.aspx

Thought to share it with others, if they hit this page in my blog and for my reference too! :)

Posted in SSIS | Leave a Comment »

Many to many relationship – White Paper

Posted by Santosh Joshi on November 30, 2011

This is one of the most sought after white paper in SSAS world -

http://www.sqlbi.com/wp-content/uploads/The_Many-to-Many_Revolution_2.0.pdf

Must read for all the SSAS developers with a fresh mind! :)

Posted in SSAS | Leave a Comment »

Performance Guide – SSAS & MDX

Posted by Santosh Joshi on November 30, 2011

Here is the link for all the white papers released for SSAS & MDX in chronological order -

1. This is the latest paper released by Microsoft in Oct, 2011 -
http://download.microsoft.com/download/6/5/6/6567C845-FC8D-4D62-920F-C027A349C889/SSASPerfGuide2008R2.pdf

2. This one was released in 2008 -
http://www.microsoft.com/download/en/details.aspx?DisplayLang=en&id=17303

3. This one is based on SSAS 2005 and was released in 2007 -
http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx

If you are thinking your career seriously in SSAS/MDX realm, these papers are “must read”.

Posted in MDX, SSAS | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.