Author: Anil

Anil http://maharjananil.com.np Highly motivated Business Intelligence Developer/Programmer having leadership abilities and team work skills as well as the ability to accomplish tasks under minimal direction and supervision. - Experience building enterprise solutions with SQL Server, SSAS, SSRS,SSIS - Excellent development and problem solving techniques in SSAS, SSRS,SSIS - Good T-SQL and database programming skills - Skill for creating great accurate and professional reports with Reporting Services - Has stable knowledge in object-oriented programming and design(C, C , Java) - Has some experience in web development by using (asp.net 3.0,C#) - Strong desire to learn new things Specialties Designing and implementing BI solutions : Microsoft Business Intelligence, SQL Server 2005/2008, Reporting Services (SSRS), Analysis Services (SSAS), Integration Services (SSIS), MDX

Watch 24 Hours of PASS Business Analytics sessions if you had missed any.

From yesterday evening to till today morning I had a great time attending the ‘24 Hour of PASS Business Analytics’ free live 12 back-to-back webcasts from various BA Conference speakers.

If you have missed out to join the live sessions on 5th Feb 2014th which had begin at 08:00PT / 11:00 ET / 16:00 GMT, then no worries the recorded webcasts sessions is available now and you can just check out those great session and am sure you will get some great knowledge in the world of BA from a great speakers around the world.

http://www.sqlpass.org/bac/2014/Sessions/SneakPeeks.aspx

You will be able to know the story behind your own DATA through these great sessions.

Also, there is a contest #pass24Hop Challenge organized for this event, where anyone can take part in the challenges and if you are lucky enough and passionate about DATA and BI then you could get a chance to win a free ticket to2014 PASS Business Analytics Conference which is going to be held on San Jose, CA from May 7-9. I think this event will be a great success and will bring more new challenges and opportunity in the world of BI, BA. Wish I could also attend this event  🙂 !!!

http://blogs.technet.com/b/dataplatforminsider/archive/2014/01/24/join-the-2014-pass-business-analytics-24hop-challenge-and-you-could-win-a-free-ticket.aspx

http://www.sqlpass.org/bac/2014/Home.aspx

Thanks,

Anil Maharjan

Some Question and knowledge learn from PASS Summit 2013 Preview

The session’s was really great and so do the Speakers . Learn a lot off stuffs and knowledge from 24 hours for PASS SUMMIT Preview.http://www.bidn.com/blogs/Anil/ssas/4520/don-t-forget-to-join-the-session-s-by-expert-today-24-hours-of-pass-summit-preview

Here are some of the question I have asked so far and by doing some research on that I found some answers as:

1.Power view loads data in Memory, so how big data set can it load and will it be significant to work/analysis as similar to multidimensional OLAP cube analysis.?

: I could not get the clear answer of this one but I think Power view can’t be used as significant as SSAS providing .

2.Will SQL server going to handle the BIG data as similar like Hadoop and others.

Since they are quite different  product I know, Microsoft Big Data offers an integrated platform for managing data of any type or size, whether it is structured data from a relational data warehouse such as SQL Server 2012 Parallel Data Warehouse, unstructured data on Hadoop or streaming data.

PolyBase is a breakthrough new technology on the data processing engine in SQL Server 2012 Parallel Data Warehouse designed as the simplest way to combine non-relational data and traditional relational data in your analysis. So, MSSQL 2012 focusing  for Big DATA , structured/ Unstructured data as like Hadoop currently emerging.

http://www.microsoft.com/en-us/sqlserver/solutions-technologies/business-intelligence/big-data.aspx

http://www.microsoft.com/en-us/sqlserver/solutions-technologies/data-warehousing/polybase.aspx

3.Can Power view used to analyze the Multidimensional OLAP cube?

I think this feature is released in SQL Server 2012 SP1 CU 4 and still not works in Excel 2013 if I am not wrong.

http://blogs.msdn.com/b/analysisservices/archive/2013/05/31/power-view-connectivity-for-multidimensional-models-released.aspx

4.Is it better to use 50-60 left join from almost 50 tables having cluster index to populate a single fact table or we can use some intermediate table using 20-20-20 joins and lastly join those tables to populate a single fact table.

: still waiting for answer

5.what will be the performance issue while replication AlwaysON High Availability and how does it sync with primary to secondary .

:This is nice feature I notice about in SQL 2012.

6.what will be better approach vertical partitioning or horizontal partitioning in SQL 2012.

:Vertical partitioning since horizontal partitioning is quite complex in SQL 2012 as per speaker.

7.Does Power view and Power Map only support Bing maps, can’t we use a Google maps.

:No, we cannot use Google maps for now.

These are some of the question I have asked so far, and the answers may not be quite enough or correct.

I hope someone knows better answer for these question too so, if you have any please share some.

That will surely helps us and wish I could attend the PASS Summit 2013 that is going to held on

Charlotte, NC: October 15-18, 2013 .

http://www.sqlpass.org/summit/2013/

Thanks,

Anil Maharjan

Think while Rename a Column Name of Table in SQL.

If someone tries to rename the column of table then we write a query as

SP_RENAME  'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'
--we use as
SP_RENAME  'DimCustomer.[FirstName]' , 'CustomerName' , 'COLUMN'
--Mistakenly,
SP_RENAME  'DimCustomer.[FirstName]' , '[CustomerName]' , 'COLUMN'
which gives the column the name [CustomerName], not CustomerName.
For rename the column 
http://blog.sqlauthority.com/2008/08/26/sql-server-how-to-rename-a-column-name-or-table-name/

You don’t use the [] escape in the second argument but by mistakenly if we use ‘[CustomerName]’ as new column name instead of CustomerName.

It sucessfully updates the column but while we select that column it’s now gives the error as

"Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong"

Also while we try to drop the column itself we cannot delete it. It also gives error as

ALTER TABLE DROP COLUMN failed because column '[CustomerName]' does not exist in table 'Customer'.

Does that mean we have to recreate the customer table since the renamed column [CustomerName] cannot use in select statement too.

Well there is a method for changing the mistakenly rename column ‘[CustomerName]’ to CustomerName . Just simple you need to go to the table DimCustomer and then click plus sign of the table and then you can see the list of columns of DimCustomer table then right click and add New Column tab which will edit all the list of columns and there you go just change the ‘[CustomerName]’ to CustomerName . This will solve the issue we are having.

columnrename

If someone by mistakenly rename the column as above and getting similar issue this post will be surely helpful.

Also, there might be any alternative T-SQL method then please suggest some idea that will surely helpful.

Thanks,

Anil Maharjan

How can we know and free remote login session in Windows Server 2008 ?

Through this post , I want to share about the problem we faced while working in Windows Server 2003/2008.

In our day to day work , we might frequently need to connect to remote Windows Server machine and while we try to connect to the Windows Server which allows multiple login session, sometimes we get an error message as : if there is no any free sessions “

If you need to connect the server urgently for your high priority task to complete then you simple need to find the sessions in that server and free one session for your login access and to complete your job. Also, Most of the already logged in users won’t even be using the session at the time and there might be some session which has been disconnected for a long time.

How can we know remote login session in Windows Server 2008?

For this,  we need to find out the RDP login sessions both active/disconnected in that particular Windows Server machine. First of all you must be logged in to your client computer as a domain Admin user and then You can use the query from the command line as:

C:\>query session /server:SERVERNAME
Or
C:\>qwinsta /server:SERVERIP

You can simple type the Server IP Address or Server Name and you will get the both active and disconnected session in that particular server as:

session

How can we free remote login session in Windows Server 2008?

From the above we can see that ahal user has a disconnected session ID#2 , so we can free this session if that user has not been doing any work in that server . you need to make sure that user hasn’t running or doing any work in offline or disconnected mode for this you can simple chat with him or just inform.Otherwise, if maharjan user  finishes his work and still in active state then you can kill one of these two session by using the query below.

C:\>reset session 2 /server: SERVERIP 
Or
C:\>rwinsta /server: SERVERIP SESSIONID

For killing the session ID#2 for ahal user terminate .

If you want to see additional information you can run the command above with [/v]

C:\>reset session 2 /server: SERVERIP  /v

Resetting session ID 2
Session ID 2 has been reset

Now , you can check the sessions in that server by simple using C:\>qwinsta /server:SERVERIP and make sure the user session is terminated and do the needful job by connecting your own session.

Hope this blog post might help for you who faces such problem in our day to day work.

Thanks,

Anil Maharjan

Date Range for SQL and Oracle.

Hello all,

This will be my memorable blog post for this year. Since, this is my last blog post for this year acc to Nepali year.We are celebrating happy new year(01/01/2070 B.S) day after tomorrow :). so, want to wish you all happy Nepali new year.

This blog is related to the Date Range. While working I need to generate the date range for Oracle side date range from start date to enddate(i.e. 2010-01-01 to 2012-01-01) . Though it’s easy for me in SQL side but in oracle side I have to do some research for this since I am not an Oracle guy.

I think there might be different alternative way for this but If you want to generate the date range from startdate to enddate, then you can simply run the script as below.

For SQL Date Range script:

--startdate: 2010-01-01 enddate :2012-01-01
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DateRange') 
DROP TABLE DateRange
GO
CREATE TABLE [dbo].[DateRange](
	DateRange DATE
)
GO
DECLARE @StartDate Date;
DECLARE @EndDate Date;
SET @StartDate='2010-01-01'
SET @EndDate='2012-01-01'
WHILE (@StartDate<@EndDate)
BEGIN
 INSERT INTO  DateRange
 VALUES(@StartDate)
 SELECT @StartDate = DATEADD(month,1,@StartDate)--for month wise increment
 --SELECT @StartDate = DATEADD(year,1,@StartDate)--for year wise 
-- SELECT @StartDate = DATEADD(day,1,@StartDate) --for day wise
END
GO
--SELECT * FROM DateRange

For Oracle Date Range Script:

DECLARE
   does_not_exist   EXCEPTION;
   PRAGMA EXCEPTION_INIT (does_not_exist, -942);
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE DateRange';
EXCEPTION
   WHEN does_not_exist
   THEN
      NULL;
END;
/      
  CREATE TABLE DateRange (
  DateRange DATE 
)   
/                      
DECLARE
    vblStartdate DATE;
    vblEnddate DATE;
BEGIN 
    vblStartdate := To_Date('2010-01-01','YYYY-MM-DD');
    vblEnddate := To_Date('2012-01-01','YYYY-MM-DD');

    WHILE (vblStartDate <vblEndDate) 
    LOOP
EXECUTE IMMEDIATE 'INSERT INTO DateRange SELECT '''||vblStartDate||''' FROM DUAL';
EXECUTE IMMEDIATE ('SELECT Add_Months('''||vblStartDate||''',1) FROM DUAL')INTO  vblStartDate;
    END LOOP;
END;
/   
--select * from DateRange

OUTPUT:

DateRange -- month wise increment from startdate to enddate
2010-01-01
2010-02-01
2010-03-01
2010-04-01
2010-05-01
..........
2011-12-01

I think this post will be helpful for some of us who is looking for such date range.

Once again Happy Nepali New YEAR 🙂 !!!

Thanks,

Anil Maharjan

The power of SIMBA into the world of BI on top of BIG DATA

After a long time gap, I would love to post this blog regarding the power of SIMBA  into the world of BI on top of BIG DATA .

It’s really interesting and exciting to see these kinds of new technology and tools on the way. Well ,I am talking about the SIMBA MDx Provider developed by the simba Technologies .

Yesterday, while I was researching regarding BIG DATA and reading some great blog post then I came to know about the SIMBA MDx Provider which seems to be a great and cool tool .

What actually the Simba’s MDx Provider :

Simba’s MDX Provider is an ODBO provider installed on the same machine as Excel. Simba also has a tool for building cube definitions, which we call schemas. These schemas are saved in XML. Simba’s schema maps MDX metadata constructs to Impala table structures. When an ODBO compliant tool such as Excel issues an MDX query, Simba’s MDX Provider maps the MDX query to HiveQL, sends the HiveQL to Impala, collects the results and returns them to the end user.

The most important technical concept is that there is no intermediate server or cube structure that caches data, all queries go direct from Simba’s MDX Provider to the Cloudera Impala server in real time.

You can find out more on this through the main blog post by samba itself through the link below:

http://blogs.simba.com/simba_technologies_ceo_co/2013/02/demo-microsoft-excel-pivottables-on-cloudera-impala-via-simba-mdx-provider.html

As the technology is in early development, it is not generally available for early testing.

Demo of Simba’s integration of doing MDX queries over Cloudera’s Impala for use with Microsoft Excel Pivot Tables .http://youtu.be/kZahPE9Puv0

Also there is a great PDF regarding the Simba Teradata case- study.

http://www.simba.com/docs/Teradata-Case-Study.pdf

Hope to see and use these kind of great tools in near future into our world of BI.

Reference :

http://blogs.simba.com/simba_technologies_ceo_co/2013/02/demo-microsoft-excel-pivottables-on-cloudera-impala-via-simba-mdx-provider.html

http://blog.cloudera.com/blog/2012/10/cloudera-impala-real-time-queries-in-apache-hadoop-for-real/

http://cwebbbi.wordpress.com/2013/02/25/mdx-on-cloudera-impala/

Thanks,

Anil Maharjan

SQL PASS Summit 2012 Hot News !!!

Hello all,

Some Hot news of the SQL PASS SUMMIT 2012.

PASS Summit is the world’s largest, most-focused, and most-intensive conference for Microsoft SQL Server and BI professionals.

Organized by and for SQL Server and BI users, PASS Summit delivers the most technical sessions, the largest number of attendees, the best networking, and the highest-rated sessions and speakers of any SQL Server event in the world.

The 3 most awaited days from Nov 7-9 are here for all the SQL and BI lovers around the world.

Currently, I couldn’t attend this session but I wish to attend this session in upcoming days or in near future summit’s a long run way to go .No worries, PASS Live TV is here for us who couldn’t make it to this SUMMIT in Seattle. The live covering of the SUMMIT can be browsed through the link below.

http://www.sqlpass.org/summit/2012/

Also, it’s nice to hear the upcoming project news from the Summit Keynotes .Apart from that ,I have read few blogs regarding the Summit Keynotes  from the great persons and want to share with you guys too .So that one can know what’s in upcoming future technologies and updates in SQL and BI field mainly.

These are some most highlighted upcoming updates in our field. You can have a look on these from the great writer blogs ( By Chris ,Marco and Denny ).

  • Project Hekaton: Project Hekaton,codename for in-memory OLTP technology with improvement in performance and scalability.
  • Polybase : Polybase allows you to query data from an external source from within the SQL Server 2012 PDW.  This will allow you to create an external table which simply links to a Hadoop cluster.By using Polybase a single T-SQL query will run queries across relational data and Hadoop data. A single query language for both. Sounds really interesting for using BigData in a more integrated way with existing relational databases.

By Chris “Wouldn’t it be cool if you could query the Facebook graph or Twitter or even Bing directly from TSQL?”Really looking forward to see these kinds of upcoming project by Microsoft SQL Server Team.

  • Updates on Columnstore: In the next major release of SQL Server the columnstore indexes will be updatable and it will be possible to create a clustered index with Columnstore index. This is really a great news for near real-time reporting needs.
  • Power View works with Multidimensional cube
  • Microsoft HDInsight Server CTP which is Microsoft’s Hadoop offering
  • SQL Server 2012 SP1 and Office 2013
  • Mobile BI and more

You can have a look in the links below for more details .

http://sqlblog.com/blogs/marco_russo/archive/2012/11/07/pass-summit-2012-keynote-and-mobile-bi-announcements-sqlpass.aspx

https://cwebbbi.wordpress.com/2012/11/08/thoughts-on-the-pass-summit-2012-day-1-keynote/#comment-6615

http://itknowledgeexchange.techtarget.com/sql-server/sql-pass-day-1-keynote/

Just want to share the updates with you guys.we will be looking forward to play around with these great products and features from Microsoft.

Thanks,

Anil Maharjan

Does Linked Measure Group allows to delete the not used Measures for that cube?

Hello all,

After long time gap, I just want to share something regarding the Linked Measure Group and which might be sure helpful to all of us mainly in the field of SSAS .

While creating and processing cube with Linked Measure Group I got certain error so posted on the MSDN and got the response .so, I think it will be good to share this .

Below is the scenario :

I have developed the Linked Measure Group in order to share data across cubes within duplicating data for sharing same Measure Group within multiple cubes .

While processing a cube I got certain error and all I want to know that

Let’s say we have a cube A and Cube B and we create a linked Measure group within cube B by using a Measure group of Cube A  and within linked Measure Group in Cube B , we delete some measures that is not needed for cube B and Processed cube B.

Then  I got certain error while processing a cube B.

But It’s strange that it won’t give any issue while deleting the dimension that linked within Linked Measure group in Cube B.

So, does Linked Measure group allows to delete the not used Measure for that cube.

If we process the cube without deleting any Measure from Linked Measures then successfully both the Cube A and Cube B processed.

Also ,just a quick do we need to process the Cube A first and then Cube B after in order to use Linked Measure Group correctly .since while processing only cube B I got error as

‘Errors in the OLAP storage engine: The metadata for the statically linked measure group, with the name of ‘Fact Table’, cannot be verified against the source object’.

Hope to know from the one who knows better in this field. Your small help will be really appreciated.

Response and findings:

It is not recommend to delete the irrelevant measures when create a linked measure group. We can set the “Visible” property to “False” to hide this measure. so, in simple we cannot delete the Measures from the Linked Measure Group.

If you add a linked measure group to a cube, the BIDS interface will let you delete some of the measures in the group (since you may not want to show all the available measures in your new cube) – however, doing this causes the above error when processing the cube! The error is basically saying that the linked measure group metadata no longer matches that of the source measure group.

The solution is that when you add a linked measure group, all the existing measures should be left intact. To hide them from the end user, set the “Visible” property for each measure

Reference:

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/1cfa1caf-2ff1-4991-a8e5-af842f287e38

http://bizintnz.blogspot.com/2009/10/ouch-nasty-ssas-linked-object-error.html

Thanks,

Anil Maharjan

Free download Microsoft Press eBook: Microsoft SQL Server 2012 Pocket Consultant.

Hello all,

I just want to share a link to download a free copy of Microsoft Press eBook ‘Microsoft SQL Server 2012 Pocket Consultant’.
This could be a great eBook for you to learn all about how to manage SQL Server .

Currently, Microsoft Press just posted the first  two chapters within this free copy as Managing SQL Servers and Policy-Based Management.

So, SQL Geeks !!! Grab your digital copy and learn all about how to manage SQL Server!

I have already downloaded and have started to read and learn the things.Hope you will too.

This link could be helpful for all of us in the field of BI-SQL server to know more about SQL Server 2012 and to manage SQL Server.

http://blogs.msdn.com/b/microsoft_press/archive/2012/07/25/free-download-managing-sql-servers-and-policy-based-management-an-excerpt-from-microsoft-sql-server-2012-pocket-consultant.aspx

Contents at a Glance

Chapter 1: Managing SQL Servers 

  • Using SQL Server Management Studio
  • Managing SQL Server Groups
  • Managing Servers
  • Using Windows Power Shell for SQL Server Management
  • Starting, Stopping, and Configuring SQL Server Agent
  • Starting, Stopping, and Configuring MSDTC
  • Managing SQL Server Startup
  • Managing Server Activity

Chapter 2: Implementing Policy-Based Management

  • Introducing Policy-Based Management
  • Working with Policy-Based Management
  • Configuring Central Management Servers
  • Managing Policies Throughout the Enterprise

Thanks,

Anil Maharjan

How can we rollback mistakenly UPDATE statement change in SQL Database.?

Hello all,

Beside than my regular helpful blog posts, it’s just a discussion type blog post that might be useful to all of us.

Let’s say we have a table customer and If someone tries to update a single row within it but by mistakenly update the entire row within a customer table. while update query someone forgot to put where condition and now all the rows of customer table are updated.

Is there any solution to rollback the update statement and retrieve all rows back for that customer table.?

After research ,I have found as the only option is to restore that previously backup Database . If our database runs in full recovery mode, we can:

* backup the transaction log

* restore the database from a full backup

* restore all transaction logs (in sequence) to the time right before the accident occurred.

Also, there is some cool tool called ApexSQL-Log which allows us to rollback the changes. But I haven’t tried it yet before but I have downloaded the product and going for a test. You can easily download from the link below: http://www.apexsql.com/sql_tools_log.aspx

So if anyone knows some idea regarding on this and if there is any solution lately with the release of MS-SQL 2012  then really want to here.

Regards,

Anil Maharjan