Power Shell Commands

 $psversiontable : Provides version of windows PowerShell

Get the cube size from PowerShell script

#### SSAS Size ###

Param($ServerName=”localhost”)

$loadInfo = [Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)

$server = New-Object Microsoft.AnalysisServices.Server

$server.connect($ServerName)

if ($server.name -eq $null) {

When below powershell query is executed, we will get cube size on the local server

Write-Output (“Server ‘{0}’ not found” -f $ServerName)

break

}

$sum=0

foreach ($d in $server.Databases )

{

Write-Output ( “Database: {0}; Status: {1}; Size: {2}MB” -f $d.Name, $d.State, ($d.EstimatedSize/1024/1024).ToString(“#,##0") )

$sum=$sum+$d.EstimatedSize/1024/1024

}

$SizeGB=$Sum/1024

write-host ‘Sum of Database = ‘$sum ‘ MB’

Write-host ‘Total Size of Cube Databases =’ $SizeGB ‘ GB’ 

Import Data to excel from tabular model using DAX

https://www.sqlbi.com/articles/import-data-from-tabular-model-in-excel-using-a-dax-query/

Import JSON from SQL

SELECT
       p.Id,
       p.FirstName,
       p.LastName,
       p.BirthDate
FROM OPENROWSET (BULK 'C:\temp\PeopleLarge.json', SINGLE_CLOB) as j
CROSS APPLY OPENJSON(BulkColumn)
WITH (
       Id INT,
       FirstName VARCHAR(50),
       LastName VARCHAR(10),
       BirthDate DATETIME
) AS p

Reference : http://www.sqlservercentral.com/articles/SQL+Server+2016/175784/?utm_source=SSC&utm_medium=pubemail

Interview Questions

Interview 1

1. Explain about the project
2. Tell me abou the challenges faced in your project
3. Different configuration options in ssis
4. Difference between union and union all
5. can we use cte inside while loop
6. diiference between row_num, rank and dense_rank
7. what are confirmed dimensions and factless fact table
8. Tell me about SCD
9. In SSRS report, I have a subreport. I do not want the data set for the subreport to be executed. How do you do it.
10. I have an excel with 10 sheets. I want data of all 10 sheets to be loaded to a single table

Interesting T - SQL Queries (T - SQL Interview Questions)

Question 1
Write a single DML statement to replace 0 with 1 & 1 with 0?

Answer
create table #temp

(nm varchar(10),

id int)



insert into #temp values

('one', 1), ('zero', 0), ('one', 1), ('zero', 0), ('one', 1),

('zero', 0), ('one', 1), ('zero', 0), ('one', 1), ('zero', 0)



select * from #temp



update #temp

set id = case when id = 1 then 0

when id = 0 then 1

end



select * from #temp


Question 2
Write a Query to get maximum 3rd salaried employee
create table #emp

(empid int,

empsal money)

insert into #emp values(1, 25000), (2, 30000), (3, 15000), (4, 40000), (5, 22000) ,(6, 18000)

select * from #emp order by empsal desc



--using top keyword
 select top 1 * from



(
  select top 3 * from #emp

order by empsal desc

)a

order by empsal asc



--using ranking function
 select empid, empsal



from
 (
  select empid, empsal, RANK() over (order by empsal desc) rnk

from #emp

)a

where a.rnk = 3



--using corelated subquery
 select *

from #emp e1

where (3-1) = (select COUNT(distinct (e2.empsal)) from #emp e2 where e2.empsal > e1.empsal)

Specifying the password of SSIS Package while executing it as SQL Job

Normally when we create SSIS packages, in connection managers we may specify the password for some connections. It can be SQL authentication for an OLEDB connection, FTP connection or something else.

When we specify such passwords which are sensitive information, either we should create a config file which stores these passwords or we have to save the package as "EncryptSensitiveWithPassword" and speficy the password for the package.

Whenever we specify a password for the package, and later when we want to execute that ETL using SQL job, we have to specify the password details even in the job.

Else, we may end up getting the below error:
Failed to decrypt an encrypted XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information.

To avoide this error, in SQL job, under "Command Line" properties, choose the option "edit the command line manually" and then type password just after "/DECRYPT".

Command line may look as below:
/FILE "D:\TestETL.dtsx" /DECRYPT pwd123  /CHECKPOINTING OFF /REPORTING E


Here pwd123 is the password for the ETL TestETL.dtsx