Speaking at SQL Server Geeks Annual Summit 2016, Asia’s Only Data & Analytics Conference.

Firstly I am happy and excited that I will be speaking at SQL Server Geeks Annual Summit 2016, Asia’s Only Data & Analytics Conference. I have been selected as a Speaker from Nepal and am representing as only one speaker from Nepal.

http://www.sqlservergeeks.com/ssgas-2016-anil-maharjan/

About the Conference:

SQLServerGeeks Annual Summit 2016 (SSGAS 2016) is Asia’s Only SQL Conference focusing on Microsoft Data. Scheduled from Aug 11-13 (Pre-Con on Aug 10) at NIMHANS, Bangalore, the summit will see 120+ sessions being delivered by 50+ speakers across 3 days. Joseph Sirosh (CVP, Data Group) will key-note the conference. Complete MS Data Platform stack is being covered at the summit. Speakers include Group PMs, Senior PMs, PMs, Premiere Field Engineers, Escalation Engineers & Data Architects from MTC. SQL CAT, SQL TIGER & Global Black Belt Team from Microsoft will deliver top-notch content and spend quality time with attendees at the convention center.

More details are here: http://www.sqlservergeeks.com/summit2016

Why one should attend this conference:

To get real-world training from industry experts
Know the latest trends in Data & Analytics world
Special focus on Analytics, Cloud & Big Data
To network & connect with the MVPs, MCMs
To learn from SQL product team, Redmond
Direct access to product team members
Benefit from new delivery formats like Open-Talks & Chalk-Talks*
Expert level demo-oriented sessions
Five parallel full-day classroom training

Please do register and be a part of this great event and follow #SSGAS2016 on Twitter for more news.

Hope to see you there and don’t forget to say hello to me in the event.

Thanks,

Anil Maharjan

Senior BI Engineer

https://www.linkedin.com/in/maharjananil

SQL Server 2016 Discovery Day – Data Visualization using R and Power BI.

SQL Server 2016 Discovery Day – Data Visualization using R and Power BI.

Last week we conducted the SQL Server 2016 Discovery Day –Release Event Kathmandu, Nepal successfully on July 9^th 2016. SQL Server Launch 2016 Event and Discovery Day is a free, one-day event where individuals come together, learn about SQL Server 2016 and solve a pre-determined problem.

https://www.eventbrite.com/e/sql-server-2016-discovery-dayrelease-event-kathmandu-nepal-tickets-25888946536?aff=efbnreg#

I got a chance again to speak in this great event and it was fun and share knowledge along with other SQL Geeks, I had given presentation on

SQL Server 2016 Discovery Day – Data Visualization using R and Power BI.

You can find my slides using below link:

SQL Server 2016 Discovery Day – Data Visualization using R and Power BI. from Anil Maharjan

Also, we had a small solution development competition in the event where we use Power BI Desktop free tool in order to create some visualization and tell some story behind data.

Below is some quick visualization that I had created in the event by using PASS SQL Saturday real Data.

Steps for Making Data visualization by using Power BI Desktop.

Step 1: Firstly, download and install the Microsoft Power BI Desktop tool, which is free from the link below also for R related charts and play around with R codes we need to download R , RStudio IDE and install too. Below is link for Power BI Desktop, R, and RStudio IDE.

https://powerbi.microsoft.com/en-us/desktop/?gated=0&number=1

https://www.r-project.org

https://cran.r-project.org/src/base/R-3/

https://www.rstudio.com/products/rstudio/download/

Step 2: Get the data related to SQL PASS, SQL Saturday from the link below:

https://drive.google.com/file/d/0BzlPwGX6UtxUNnlfZ01KczF0NHc/view

This zip data files contains the different .sql files with data included within scripts.

Step 3: Load those scripts into SQL Server Database and then use Power BI Desktop ‘Get DATA’ tab to load the Data into Power BI Desktop and do visualization.

Alternative: If you haven’t install the SQL server Database then you can use a single file such as ‘dbo.SQLSatSessions.Table.sql’ and then Excel file to manipulate the data in your required format.

Step 4: Load the only data part from the file ‘dbo.SQLSatSessions.Table.sql’ into excel as below

Then use Text to Columns tab in DATA tab section as below to make the data into proper column format.

Once you prepare your data as below format then now you can start analysis using Power BI Desktop.

Step 5: Use Power BI step 3 or Step 4 to load the data into Power BI. We have use step 4 here so now use Power BI to load data from Excel source then you will get data columns in right hand side as below.

Step 6: Now start visualization using Power BI Desktop free tool, here we are using data of SQL Saturday session details and prepare different line chart, tree map, filled map , Table , R script Visual different charts that we have used to show different visualization details as below.

One can learn how to create these different charts by going through below links

https://powerbi.microsoft.com/en-us/documentation/powerbi-service-visualizations-for-reports/

Step 7: For now just for sample how we can simply create a line chart by just drag and drop feature of Power BI Desktop.

Just go to right side of Power BI Desktop and select EventDate and SQLSATURDAY filed and drop Eventdate in Axis and SQLSATURDAY into Values section and change the SQLSATURDY value as count by just clicking on SQLSATURDY field in Values section.

Just simple so you can try these different charts by simply drag and drop feature.

For R script Visual you need to know some R language first in order to create R visualization chart in Power BI . One can learn R from https://www.r-project.org/

Step 8 :Publish these reports on Power BI cloud service by just clicking Publish button from Power BI Desktop , also now one can also publish these reports to publicly in web. Once you publish your report in Power BI service go to Reports -> Your Report -> File -> Publish to web . After that anyone can go through these reports publicly in the web.

Also, URL that you get through publish to web can be embedded to your website along.

Summary:

In Summary, from these above Power BI Desktop Visualization it helps to tells us that which SQL Saturday is conducted in which state, country in which year and a particular Day along with total sessions conducted. It also helps us to tell us that SQL Saturday trends year on year and mostly in September month most of SQL Saturday is conducted as per SQL Saturday data history.

Also, it helps to tell the distribution of state wise SQL Saturday conducted states .which shows most of SQL Saturday happens in North America.

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

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

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

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

Download a free copy of Microsoft Press eBook ‘Introducing Microsoft® SQL Server 2012’

Hello all,

After a long time gap, I just want to share a link to download a free copy of Microsoft Press eBook ‘Introducing Microsoft® SQL Server 2012’.

This could be a great eBook for you to learn and know about what’s inside the ‘ SQL Server 2012 ’ released by Microsoft.

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 , virtual lunch event-sessions and more.

http://www.microsoft.com/sqlserver/en/us/default.aspx

Contents at a Glance

Part I Database Administration (by Ross Mistry)

1. SQL Server 2012 Editions and Engine Enhancements
2. High-Availability and Disaster-Recovery Enhancements
3. Performance and Scalability
4. Security Enhancements
5. Programmability and Beyond-Relational Enhancements

Part II Business Intelligence Development (by Stacia Misner)

6. Integration Services
7. Data Quality Services
8. Master Data Services
9. Analysis Services and PowerPivot
10. Reporting Services

Regards,

Anil Maharjan

How to drop/delete all the user define tables within a SQL database.

Hello all,

Firstly on this lovely Valentine’s day esp, I wish Happy Valentine’s day to all the loved ones 🙂 .

I would like to post some another blog which might be helpful to the one who works in SQL.

Sometimes we might need to delete all the user define tables from a Database in SQL. So, If any one just need to delete all the user define tables from a SQL DB then you can use just a simple Query as below:

USE Database:--replace with your Database name.
GO
DECLARE @tbl_name as varchar(256)
DECLARE @flag as int
DECLARE @name as varchar(256)
set @flag=(SELECT count(*) FROM sysobjects where type='U')
while(@flag>0)
BEGIN
set @name=(SELECT top 1 name FROM  sysobjects where type='U')
set @tbl_name = 'drop table ' + @name
exec(@tbl_name)
print @tbl_name
set @flag=@flag-1
end

within a database we can have objects as TABLES, VIEWS, FUNCTIONS, PROCEDURES, TRIGGERS, etc. are there. So, If anyone just want to delete only User Define TABLES without dropping the entire Database and other objects the above query is the best one. If someone drops the database that means those objects needs to be recreated. So,in order to save the time and for quick approach I found this useful .

This is just a simple example and looks simple but it might help for the one who indeed needs such a solution.

Regards,

Anil Maharjan

How to delete a particular row from a table in SQL.

Hello all,

After such a long time , I would like to post some another blog which might be helpful to the one who works in SQL.

Sometimes we might need to delete the particular row from a table in SQL Database.

While working someone might mistakenly insert the duplicate same data multiple times within a table that might contains a huge amount of data and after populating that table .He/she wants to delete that duplicate data inserted from a table.

So, for this we might drop and recreate the table or truncate the table and insert again. But, if it is a large table then it would be better to find the duplicate row within a table and delete duplicate one which helps in time consumption and server utilization too.

In order to obtain this , we can simple used a simple query as

WITH newtbl as(SELECT *, 'row number'=ROW_NUMBER() over (order by FirstName) FROM CustomerList)
DELETE FROM newtbl WHERE [row number]=2

--where CustomerList is a user table having dublicate record.

This is the tested script for Test Database where CustomerList table have duplicate records which needs to be delete.

This is just a simple example and looks simple but it might help for the one who indeed needs such a solution.

Regards,

Anil Maharjan