Monday, February 19, 2018

SQL Server | DB Restore Error

When you are working on the task of database backup and restores, you might have error while restroe the database "database in use". This error populate because the current database is in use on same machine or over the network. There can be below cases:
1. On SQL Server database query window is open
2. Database query window will be open on any other computer in network
3. Database is currently connected with application

So in order to restore the database you need to disconnect the application from you machine or from another machine over the network.

Now, it is easy to disconnect the query window which is opened in your machine

It is difficult for you if any body using database over the network. Then how to disconnect all the connections


DECLARE @query_v nvarchar(4000) = '';  
SELECT @query_v = @query_v + 'kill ' + CONVERT(varchar(10), session_id) + ';' FROM sys.dm_exec_sessions WHERE database_id  = db_id('Your_database_name')

Note * - 
change the database name in above query, for which you want to restore the database
After change the database name you need to execute the above query

While query execution you can have below error

Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.

Make sure while executing above query, close the current database query window or connect to "master" database

Tuesday, January 23, 2018

MACRO to List Out All Tab Names Along With Field Header Name

Some times we need spreadsheet information at one place, information like :
  1. All tab Names available in spreadsheet
  2. All Header Names
This is because one spreadsheet can have many tabs in it and it is not sure that all tabs having same number of header columns. This article can be helpful who are looking for all information at one place. This kind of information is useful in automation programs, to write/update the spreadsheet through automation.

More possibilities are at run time we are not aware of the structure of spreadsheet.

This kind of information further can be used for some automation process (specially in IT industry), since automation can save TIME and MONEY.

One approach, i tried we can use Pentaho Tool (Open Source ETL Tool) for this, but some where this tool not use full where spreadsheet size cross 1GB. Because in my end Pentaho needs 24GB of JVM space to extract data from spreadsheet.

To achieve this we have designed Macro VBA program, which can perform this tasks in minutes, no matters what the size is.

Pre-requisite :
Save your Spreadsheet in macro enable format, if it is not.

Macro Code:

Sub ListMeta()
    Dim Ws As Worksheet
    Dim MetaWS As Worksheet
    Dim HeaderRange As Range
    Dim FieldCount As Integer
    Dim RowNo As Long
    Dim Spreadsheet As Workbook

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    ''''''******* Delete sheet "MetaData-Sheet" ********** 
    Application.DisplayAlerts = False
    On Error Resume Next
    On Error GoTo 0
    Application.DisplayAlerts = True

    Set Spreadsheet = ThisWorkbook
    Set MetaWS = Spreadsheet.Worksheets.Add
    ' Add Metadata Sheet
 MetaWS.Name = "MetaData"

    RowNo = 1

    For Each Ws In Spreadsheet.Worksheets
        If Ws.Name <> MetaWS.Name And Ws.Visible Then
            FieldCount = 1
            RowNo = RowNo + 1
            'Copy the sheet name in the A column
            MetaWS.Cells(RowNo, 1).Value = Ws.Name

            For Each HeaderRange In Ws.Range("A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1")  ' you can change WS range as per your requirement
                FieldCount = FieldCount + 1
                MetaWS.Cells(RowNo, FieldCount).Formula = _
                "='" & Ws.Name & "'!" & HeaderRange.Address(False, False)
            Next HeaderRange

        End If
    Next Ws


    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

Friday, September 29, 2017

QV30 Qlikview Interview Questions

Qlikview is powerful BI tool used to create report, dashboards and this tool is capable of showing real time report or snapshot based reports.

Real time report - data is directly coming from OLTP database
Snapshot reports - Qlikview collect data in QVD's, which needs to refresh on daily/weekly basis

Below are the some of qlikview questions which can be asked by any interviewer.

QlikView Interview Questions:

QlikView Architecture:
1. Optimized and Un-optimized QVD Load Situations?
2. 3 tier architecture implementation
3. How does QlikView stores data internally?
4. Restrictions of Binary Load?
5. How are NULLS implemented in QlikView?
6. How do you optimize QlikView Application? (What tools are used and where do you start?)
7. What is the difference between Subset ratio & Information Density?

QlikView Scripting:
1. What is the difference between ODBC, OLEDB & JDBC?
2. What is the use of Crosstable prefix in QlikView Load Script?
3. What is Mapping Load & ApplyMap()?
4. What is the difference between Map Using and Mapping Load?
5. Synthetic keys in QlikView and how & when to avoid them?
6. Difference types of Joins in QlikView?
7. What is the difference between Join and Keep?
8. How do you use Having clause (SQL Equivalent) along with Group By in QlikView?
9. Explain IntervalMatch function in QlikView?
10.Explain Concatenation, No Concatenation & Auto Concatenation?
11.Explain how to implement Incremental Load?
12.What is Circular Loop and how do you avoid it?
13.Explain Exists() function in QlikView and when do you use this function?
14.What is Generic Load in QlikView?

QlikView Expression Language / UI:
1. Explain Aggr Function?
2. What is the use of FirstSortValue in QlikView?
3. What are Set Modifiers and Set Identifiers?
4. What is P() & E() and where do you use them?
5. What is the difference between ValueList() and ValueLoop()?
6. What is Partial Reload? Why do you use “ONLY” Qualifier?
7. Difference between Cyclic Group & Drilldown Group?
8. Explain Alternate States? Where do you use them?

QlikView Security:
1. Describe Section Access Architecture?
2. Difference between Authentication & Authorization in QlikView? How to implement them?
3. Difference between File System Security vs Section Access?
4. Explain “Strict Exclusion” while implementing Section Access? What are the implications of not using them?
5. How do you implement Section Access on hierarchy based data?

QlikView Server and Publisher:
1. What are the multiple protocols defined for client communication with QVS?
2. Explain different communication encryptions for Windows Client & AJAX Client?
3. What is the use of Anonymous User Account in QVS?
4. What are the different types of CALS and Explain them?
5. What are the different editions of QlikView Server?


1. Difference between RDBMS and Associative Database?
2. Ragged hierarchies in Data Ware Housing?
3. Explain EAV data Modelling Technique?
4 What are slowly changing dimensions?

Questions About Roles

1. Whats your role in your project
2. Whats your daily activity in your project
3. Requirement Gathering
4. One can ask About your project
5. What is the major KPI s in your project
6. What is Dashboard
7. Which  architecture are you using in your project
8. What type of schema are you using in your project
9. Difference between Star and Snowflake schema
10. What is Peek, Previous, Apply map, Interval Match
11. Whats is the difference between Pick and Match
12. What is Fuzzy search
13. How many charts are you used till now
14. Have you used macros in your project and why
15. Whats the difference between QV versions
16. Have you used Alternate states and why
17. How many dimensions are used in Guage Chart
18. What is set analysis
19. Best data modelling techniques
20. You have any knowledge about QV server and Publisher
21. What is Incremental Load
22. Have you Create Ad-hoc reports in your project And how
23. Any knowledge about Data Island
24. DMS authorization
24. Section Access
25. Conditional enabling
26. Difference between join and keep
27. Difference between Concatenate and join
28. Binary Load, Preceding Load, Partial Load
29. How many ways you can maintain to store the QVD's

1. Qlikview features
2. What is Circular loops
3. What is Synthetic key, is it good or bad having?
4. What P() and E() in Set analysis?
5. What is Comparative analysis?
6. What is Mekko chart and what is the difference between bar and Mekko chart?
7. What is the difference between Pivot, Straight and Table box?
8. How you connect to Database?
9. What are the various data sources for Qlikview?
10. What is partial reloading?
11. How you refresh you dashboards periodically?
12. What are the different types of CALs available?
13. What are the various joins available in Qlikview?
14. How you use Macros in Qlikview?
15. How you optimize Qlikview dashboards?
16. What care should be taken while designing a datamodel?
17. How you test your Dashboard?
18. Types of authorization in Qlikview?
19. Difference between Join and Concatenate?
20. What is NoConcatenate?

Wednesday, March 15, 2017

failed to load the jni shared library

I am here listing some of the error while installation of Spago BI studio, i am getting these error and here i am writing the problem and solution, so that other can not stuck cause of small error.

After installation of java 1.6 from post, i tried to start SpagoBIStudio_5.1.0 using below command

SpagoBI.exe -vm "C:\Program Files (x86)\Java\jdk1.6.0_30\bin"

it failed with error "failed to load the jni shared library"

Problem :
incorrect version of java used

Solution :
I am trying to start 64 bit version of spagoBI server with 32-bit version of java.
install 64-bit versionof java and then try below command

SpagoBI.exe -vm "C:\Program Files\Java\jdk1.6.0_30\bin"

java was started but returned exit code 13

java was started but returned exit code 13

This problem occur during startup of SpagoBIStudio_5.1.0 studio. In my case i tried to start SpagoBI studio for makeup of my reports, but it never start. Then i go through the logs generated by application and it gives error "java was started but returned exit code 13", after working with colleagues and google i identified the solution finally, please look the solution below.

I have installed java (1,7/1.8) on system, because of spagoBI compatibility issue, was using incorrect version of java.

Install correct version of Java (in my case it is 1.6)
web stats