How do I prepare myself to be a data analyst?

Standard

Originally published on Quora: How do I prepare myself to be a Data Analyst?

Based on how you are framing your question, it seems that you currently don’t have “Data Analysis” Background but want to build a career in this field. Here are three things you could do:

  1. Learn Tech Skills: You will need technical knowledge to be successful at analyzing data. SQL and Excel are a good starting point. You could do a lot with these tools — then depending on the bandwidth that you might have you could explore R. How do you learn this? Here’s a learning pathway: Learn #Data Analysis online – free curriculum ; Also search for free courses on Coursera or other platforms.
  2. Learn Soft/Business Skills: This is as important as tech skills (if not more!) when it comes to Data Analysis. Finding Insights from your data is half the battle, you will need to put the insights in a context/story and influence business decisions and sometimes influence business change. we know change is always hard! So your soft/business skills will be very important. Also, you will benefit a lot from learning about how to break down problems, communicate your solution by using “business” language vs tech-speak.
  3. Apply them (and keep improving): Now that you have picked up some tech and soft/biz skills, apply them! Get an internship, Help out a non-profit in your free time (Data Kind, Statistics Without borders, Volunteer Match are good resources to find a non-profit) and start applying your skills! It would also help you get some “Real” world experience and applying what you have learned while “learning-on-the-job” is arguably the BEST way to pick something up!

Hope that helps!

SQL Server Reporting services: How to display “There are NO rows” message?

Standard

Problem:

You have a SQL Server reporting services (SSRS) report that has a table which displays some records — but sometimes it can have NO rows; In that case, how to display “There are No rows” message so that it doesn’t confuse the consumer.

Solution:

  1. Open the report in SQL Server Data Tools and go to the “design” tab of your SSRS report
  2. Select your table (do NOT select a cell inside a table. Make sure that the table is selected) SQL Server reporting services NO data rows message
  3. While the “table” is selected, Go the Properties section OR you can use F4
  4. Inside the Properties section, find “No Rows” section and you should see a NoRowsMessage property:SQL Server reporting services NO data rows message v2
  5. Go to the preview tab to make sure it’s working and you should be ready to deploy the change!

That’s it! Hope that helps.

Official reference:  https://msdn.microsoft.com/en-us/library/dd220407.aspx

Author: Paras Doshi

Back to Basics — What is DDL, DML, DCL & TCL?

Standard

I was talking with a database administrator about different categories that SQL Commands fall into — and I thought it would be great to document here. So here you go:

ACRONYM DESCRIPTION SQL COMMANDS
DML Data Manipulation Language: SQL Statements that affect records in a table. SELECT, INSERT, UPDATE, DELETE
DDL Data Definition Language: SQL Statements that create/alter a table structure CREATE, ALTER, DROP
DCL Data Control Language: SQL Statements that control the level of access that users have on database objects GRANT, REVOKE
TCL Transaction Control Language: SQL Statements that help you maintain the integrity of data by allowing control over transactions COMMIT, ROLLBACK

BONUS (Advance) QUESTION:

Is Truncate SQL command a DDL or DML? Please use comment section!

Author: Paras Doshi

Productivity Tip: Learn to Comment/Uncomment SQL code using shortcuts

Standard

I spend a lot of time writing SQL code — and as a reader of this blog, You might be in the same boat. So any productivity gains that we could get here could go a long way. On that note, here’s a quick productivity tip: Learn to comment/uncomment multiple lines of SQL code using keyboard shortcut.

COMMENTS SQL CODE

If you are using SQL Server Management Studio, it’s “CTRL-K followed by CTRL+C” for commenting AND “CTRL+K followed by CTRL+U” for uncommenting.

If you are using some other Data Management Software tool, I am sure you can find it using their HELP section or googling around.

Either ways, these shortcuts go a long way in making you more productive! What is your favorite productivity tip?

SQL: How to get first/last transaction details for each user account?

Standard

Looking at user’s first/last transaction details is a common requirement. So given that you have a “user_transaction_details” table which looks something like:

Transaction_id | user_id | transaction_date | purchase amount

How would get first/last transaction details for each user account?

Here’s a design pattern:

[code language=”SQL”]

select AllTransactions.user_id,AllTransactions.purchase_amt from user_transaction_details AllTransactions
inner join
(
select user_id, min(transaction_timestamp) as first_transaction_timestamp from user_transaction_details
group by user_id
) FirstTransaction
on AllTransactions.user_id = FirstTransaction.user_id and AllTransactions.transaction_timestamp = FirstTransaction.first_transaction_timestamp

[/code]

To get the last transaction details for each user account, replace min function with max and that should be it.

Note: Depending on your data model and how you used it in the join, it might be that there would be multiple rows marked as “first/last” transaction and so would want to spend some time figuring out how to deal with these multiple row situation especially if you’re planning to further join this data.

Conclusion:
In this post, I shared a design pattern to write a SQL query to get first/last transaction details for each user account type.

Question for you:
How would you solve this problem? Share you query in the comments section!

SQL Server reporting services: How to customize the Legend Text on a chart?

Standard

Problem Statement:

How do you customize the Legend Text on a chart? by default, it’s going to show you the name that you have in the Data Set, but sometimes that’s not what you want to display so how do you change the name?

It’s really useful when you don’t want to change the name of field in the dataset or when you are using an expression as an item on the chart and you need to display a business user friendly name on the report:

SSRS Custom Legend TextSolution:

1. Go to Chart Data Properties:

2. To change the Legend Text of “Sales Amount CY” – Navigate to series properties of the “Sales Amount CY” series:

SSRS Chart Series Legend Properties

3. Go to “Legend” and enter the Text in the “Custom Legend Text“:

SSRS Custom Legend Text Series Properties legend custom4. You can customize text for all legends on your report by navigating to the series properties.

SSRS Custom Legend Text Change Name

Conclusion:

In this post, I walked you through how you can customize the legend name/text in SQL Server Reporting Services.

SQL Server: How to insert explicit values into an identity column of a table?

Standard

In a SQL server data mart, it’s common to have an Identity column (SK columns) in a Fact Table. And it’s also common to add a -1 record to this table for “unknown values”. So if you want to insert an explicit value into an identity column in sql server table, here are the steps:

1) In SSMS, select the Table the Object Explorer > Right click > Script Table as > Insert To > “New Query Editor Window”

2) This gives you a nice starting point to created your identity insert script:

3) Add following code before the insert statement:

SET IDENTITY_INSERT <schema_name>.<table_name> ON
go

4) Add following code after the insert statement:

SET IDENTITY_INSERT <schema_name>.<table_name> OFF
go

5) Now modify the “VALUES” section of the insert statement. Also, Note that the identity column would not be in column list, you’ll have add it manually in the script.

After the values are added, here’s what the code should look like:

[code language=”sql” gutter=”false”]

USE
GO

SET IDENTITY_INSERT <schema_name>.<table_name> ON
go

INSERT INTO <schema_name>.<table_name>
([Col1_sk]
,[col2])
VALUES
(-1,
‘N/A’)
GO

SET IDENTITY_INSERT <schema_name>.<table_name> OFF
go

[/code]

Conclusion:
I hope this gives you a good starting point to create an identity insert script in SQL server table.