Time Intelligence Functions

 TOTALYTD

TOTALQTD

TOTALMTD

DATESYTD

DATESMTD

DATESQTD

DATEADD

SAMEPERIODLASTYEAR

PARALLELPERIOD

DAX Functions

 USERELATIONSHIP

generates table

-----------------------------------------------

DATATABLE

UNION

SUMMARIZE

DISTINCT

FILTER 

GENERATESERIES

CALENDAR

CALENDARAUTO

--------------------------

Aggregate Functions:

SUM

MIN

MAX

AVERAGE

COUNTROWS

DISTINCTCOUNT

-----------------------------------

Information Functions:

ISBLANK

ISFILTERED

ISCROSSFILTERED

----------------------------------------

Logical Functions

IF

AND

SWITCH

------------------------------------------

Date & Time Functions

DATEDIFF

DATEVALUE

-------------------------------------------

FILTER Functions

CALCULATE

FILTER

VALUE

ALL

TOPN

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