Be an Analyst, YouTube Series

If you always have a hesitation which videos channels you want to follow to enhance your skills as an analyst.

    1. Keshav singh
    Keshav singh mainly concentrates on Database & Business Intelligence topics. recently, he started posting case studies in Business Intelligence.
    3. Gerard Verschuuren
    Verschuuren assisted scientists and engineers in using computers for data analysis, statistical analysis, and regression analysis. He became a Microsoft Certified Professional and was an official adviser on Excel’s latest statistical functions

Using SQLCMD utility to work with SQL Server 2012

If you are one those who likes to work with command prompt. Then this article will help you to get started.

Steps :

Method 1 :
Open the command prompt (OR) press CTRL+R and type CMD it will open the command prompt.
CTRL+R
Once you opened the command prompt type SQLCMD. If you have default instance it will open the SQLCMD utility to work with SQLServer otherwise, you will get an error.

SQLCMD
SQLCMD

Method 2 :

Now you can use

SQLCMD -S Servername\Instancename -E

In the above command -S(Capital Letter) to specify the Server name and Instance name , -E is the default option to login with the windows authentication.

Method 3:

If you want to run as the SQL Server authentication. then, you can use the below command

SQLCMD -S Servername\Instancename -U SqlUsername -P SqlPassword

Once you specified the -U, you are not supposed to use windows credentials. this option is those who want to work with SQL Server Logins

Working Examples

Use GO with after every command you type in Command Prompt.

To know the SQL Version you are running.

SELECT @@version
GO

To know the SQL Server name and Instance name.

SELECT @@servername
GO

To know about the databases

SELECT name from sys.databases
GO

Be an Analyst, Edition-4

Be an Analyst is weekly series which aimed to enhance skills beyond beginner level.These topics are composed of the most popular articles, as well as articles demonstrating new or updated features

    2. Changing database ownership
    when we create the database on SQL Server instance, SQL Server engine sets the login that created the database as the default database owner. This gives the user full control on the database, meaning he/she can do whatever they like on that particular database. According to the SQL Server security best practice document, we should grant login with the least amount of privileges. Therefore, it is recommended to set built-in sa account as an owner of all databases on SQL Server instance. This will make database and server more secure.

Be an Analyst, Edition-3

Be an Analyst is weekly series which aimed to enhance skills beyond beginner level.These topics are composed of the most popular articles, as well as articles demonstrating new or updated features

    3. Show specific sheets in excel
    In a workbook with lots of worksheets, you can have a hard time finding the sheets that you need, to do a specific task. Instead of scrolling through all the sheet tabs, or using the popup list of worksheets, use a drop down list to show specific sheets in Excel. Also, all other sheets are hidden, and you’ll be able to focus on what you need to do, and ignore everything else.
    5. Fetching first N rows
    using FETCH FIRST n ROWS syntax to get a subset of the rows from what would normally be a larger resultset. for those who have experience in working with python pandas library. it is like head()

Using Vlookup and ImportRange to Pull data from one workbook to another

If you are new to Google spreadsheets. Then, it is bit different to use Vlookup to pull data from one workbook to another than in Microsoft Excel.Please read this article for those who need an introduction to ImportRange.

Case Study:

For suppose,if you have employee details and which you are using for marketing.Recently, a friend of yours has shared a file with you to check how many employee details has existed in your records. The file which you have is Employee Details for marketing and the file your friend shared with you is Check Details. For your reference have a look at below image.

Using Vlookup and ImportRange to pull data from one workbook to another
Using Vlookup and ImportRange to pull data from one workbook to another

Insert the below formula after adding the new column in the Employee Details for Marketing workbook. whenever there is match you see an employee name from another workbook. Use the filter to find out how many employees has matched.

=VLOOKUP(A2,
IMPORTRANGE(“1Xx133HsZNdyay9Fvwe1tTv5icfvDT0AVSRahCBs97Os”,”EmployeeData!$A$1:$F$100″),1,0)

In the above formula , A2 is lookup value , and then Lookup range , column index number.

For Lookup range, we are using ImportRange function to import data from another workbook.

Exporting Data from Access to Excel

Microsoft Access is a Relational Database Management System(RDMS).which can organise information entity wise and provide efficient retrieval with the help of queries.

In this article, we will practice how to export data from Access to Excel. For that,we downloaded some dummy data from generatedata website.The dummy data has the following fields Name, Email, Company, OrganisationNumber, Phonenumber

Importing Data from Access to Excel
Employee Data

In the Ribbon,you can click External Tab and then click Excel under Export group. ( Don’t confuse with Excel under Import group. this option is for importing data from Excel to Access).

Now, It will open the Excel export wizard,and select the required options as per below image to export the data.

Exporting Data to Excel form Acess
Export Dialog to Excel

Finally, you will see a wizard confirming whether you want to save your export steps.

When to use Save Exports :

A simple case study to explain the save exports scenario. For suppose,If you are working as an analyst and handling Operational database which includes data regarding business activities.every day , if you want to forward the category wise data in the operational database to concerned manager.

write a query against the operational database as per the requirements from the concerned manager. and then,save export steps option. which will automate your exports by running macros.

Note :

    If you want to export data to existing workbook.please close the preferred workbook and then run the above steps. the name of workbook should be the name of the Access table or Query.

Be an Analyst, Edition-2

Be an Analyst is weekly series which aimed to enhance skills beyond beginner level.These topics are composed of the most popular articles, as well as articles demonstrating new or updated features.

    1. Best python pandas features
    A quick starting guide to those who want to learn pandas.Pandas is the most widely used tool for data munging. It contains high-level data structures and manipulation tools designed to make data analysis fast and easy.

Be an Analyst, Edition-1

Be an Analyst is weekly series which aimed to enhance skills beyond beginner level.These topics are composed of the most popular articles, as well as articles demonstrating new or updated features.

    1. Using Auto filter with a Pivot Table
    Have you ever tried of applying AutoFilter in Microsoft Excel and finally end up with disappointing ?. then this is the article for you which will explain how to apply AutoFilter
    3. Why Use Access, Why not use Excel
    In this video from Stephen Brower , Explained why to use access rather than Excel. If you are working with data, which is similar to what Mr.Browner explained. then it’s better to work with access which will eradicate errors using referential integrity.