Pages

Monday, March 20, 2017

Use SQL to read XML data - Example


There are a few ways to read XML data using SQL and following example demonstrate how to read a simple XML file in tabular format.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
DECLARE @MyXML XML
-- SET @MyXML = (SELECT CONVERT(xml, BulkColumn, 2) xml 
-- FROM OPENROWSET(Bulk 'C:\sample.XML', SINGLE_BLOB) [rowsetresults])
SET @MyXML = '
<Order xmlns="XMLSchema">
  <Database>
    <Tables>
      <Table Name="Product">
        <Files>
          <File FileName="Product.txt" NumberOfRows="17" RowDelimiter="{LF}" />
        </Files>
        <Columns>
          <Column Name="ATC_1_CD" Length="30" DataType="nvarchar" />
    <Column Name="IMS_PROD_SHRT_NM" Length="80" DataType="nvarchar" />
          <Column Name="PACK_DESC" Length="80" DataType="nvarchar" />
        </Columns>
      </Table>
   <Table Name="Corporation">
        <Files>
          <File FileName="Corporation.txt" NumberOfRows="5" RowDelimiter="{LF}" />
        </Files>
        <Columns>
          <Column Name="CORP_ID" Length="30" DataType="nvarchar" IsPrimaryKey="true" />
          <Column Name="CORP_SHRT_NM" Length="80" DataType="nvarchar" />
        </Columns>
      </Table>
    </Tables>
  </Database>
</Order>
'
;WITH XMLNAMESPACES (DEFAULT 'XMLSchema') -- Define Default XML schema (i.e. xmlns )
SELECT 
 FileName  = files.x.value('@FileName','varchar(200)'),
 TableName  = tabs.x.value('@Name','varchar(200)'),
 ColumnName  = cols.x.value('@Name','varchar(200)'),
 ColumnDataType = cols.x.value('@DataType','varchar(200)'),
 ColumnLength = cols.x.value('@Length','varchar(200)')
FROM
     @MyXML.nodes('/Order[1]/Database[1]/Tables[1]/Table') tabs(x) 
CROSS APPLY tabs.x.nodes('Columns[1]/Column') cols(x) -- Loop through Columns level
CROSS APPLY tabs.x.nodes('Files[1]/File') files(x) -- Loop through Files Level

Result


Reference

Monday, March 7, 2016

Dynamic Management Views for SSAS

Analysis Services (SSAS) Dynamic Management Views (DMV) are query structures that expose information about local server operations and server health. The query structure is an interface to schema row sets that return metadata and monitoring information about an Analysis Services instance.

SSAS DMVs can be used to monitor the server resources (e.g. connections, memory, CPU, users, aggregation etc. ) and find out the structure of SSAS databases (e.g. hierarchy , dimensions, hierarchies, measures, measure groups, data sources, cubes, actions and KPIs etc.) .

SQL statements can be used to query the row sets, but have following limitation in SQL 2008 version.
  • SELECT DISTINCT does not return DISTINCT values
  • ORDER BY clause accepts just one field to order by. Only one order expression is allowed for TOP Expression at line 1, column 1″
  • COUNT, SUM does not work
  • ORDER BY <number> does not ORDER, but no error
  • JOINS appear not to work
  • LIKE does not work
  • String functions like LEFT do not work
Usefull DMVs
SELECT * FROM $system.DBSCHEMA_CATALOGS -- list of the Analysis Services databases on the current connection.
SELECT * FROM $system.DBSCHEMA_COLUMNS
SELECT * FROM $system.DBSCHEMA_PROVIDER_TYPES
SELECT * FROM $system.DBSCHEMA_TABLES

SELECT * FROM $system.DISCOVER_SCHEMA_ROWSETS
SELECT * FROM $system.DISCOVER_COMMANDS
SELECT * FROM $system.DISCOVER_CONNECTIONS
SELECT * FROM $system.DISCOVER_JOBS
SELECT * FROM $system.DISCOVER_LOCKS
SELECT * FROM $system.DISCOVER_MEMORYUSAGE ORDER BY MemoryUsed DESC
SELECT * FROM $system.DISCOVER_OBJECT_ACTIVITY
SELECT * FROM $system.DISCOVER_OBJECT_MEMORY_USAGE 
    ORDER BY OBJECT_MEMORY_NONSHRINKABLE DESC
SELECT * FROM $system.DISCOVER_SESSIONS

SELECT * FROM $system.MDSCHEMA_CUBES
SELECT * FROM $system.MDSCHEMA_DIMENSIONS
SELECT * FROM $system.MDSCHEMA_FUNCTIONS
SELECT * FROM $system.MDSCHEMA_HIERARCHIES
SELECT * FROM $system.MDSCHEMA_INPUT_DATASOURCES
SELECT * FROM $system.MDSCHEMA_KPIS
SELECT * FROM $system.MDSCHEMA_LEVELS
SELECT * FROM $system.MDSCHEMA_MEASUREGROUP_DIMENSIONS 
    -- WHERE MEASUREGROUP_NAME = 'NSA NZ Sales'
SELECT * FROM $system.MDSCHEMA_MEASUREGROUPS
SELECT * FROM $system.MDSCHEMA_MEASURES
SELECT * FROM $system.MDSCHEMA_MEMBERS
SELECT * FROM $system.MDSCHEMA_PROPERTIES
SELECT * FROM $system.MDSCHEMA_SETS

Thursday, February 18, 2016

PowerShell - Get System information



Get PowerShell Information
$Host

Get Computer System Information
1
2
3
4
5
# Useful Computer System Info.
Get-WmiObject -Class Win32_ComputerSystem -ComputerName . | `
    SELECT PSComputerName, BootupState, Status, Domain, UserName,  Manufacturer, `
    NumberOfLogicalProcessors, NumberOfProcessors, SystemType, `
    @{n="Memory(MB)";e={[math]::ROUND($_.TotalPhysicalMemory / ( 1024 * 1024) -as [Float],2)}} #,*

Get Operating System Information
1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# Useful Operating System Info.
Get-WmiObject -Class Win32_OperatingSystem -ComputerName DI21 | `
    Select-Object -Property PSComputerName, Caption,  CodeSet, CountryCode, 
    CreationClassName, CSCreationClassName, CSDVersion, CurrentTimeZone,  
    EncryptionLevel, ForegroundApplicationBoost,  InstallDate, LastBootUpTime, 
    LocalDateTime, Locale, Manufacturer, MaxNumberOfProcesses, MaxProcessMemorySize, 
    MUILanguages, NumberOfLicensedUsers, NumberOfProcesses, NumberOfUsers, 
    OperatingSystemSKU, OSArchitecture, OSLanguage, OSProductSuite, OSType, 
    ProductType, RegisteredUser, SerialNumber, ServicePackMajorVersion, 
    ServicePackMinorVersion, SizeStoredInPagingFiles,  SuiteMask, 
    SystemDirectory, Version, PSStatus, BuildNumber, BuildType,
    @{n="TotalVirtualMemorySize(MB)";e={[math]::ROUND( $_.TotalVirtualMemorySize / (1024*1024) -as [Float], 2)}}

Get Processes' Information
1
2
3
4
5
6
7
8
9
Clear-Host
Get-Process -ComputerName . | SELECT ProcessName, `
 @{n="WS(MB)";e={[math]::ROUND($_.WS / ( 1024 * 1024) -as [Float],0)}}, ` # WS,
 @{n="VM(MB)";e={[math]::ROUND($_.VM / ( 1024 * 1024) -as [Float],0)}}, ` # VM,
 @{n="PM(MB)";e={[math]::ROUND($_.PM / ( 1024 * 1024) -as [Float],0)}}, ` # PM,
 @{n="CPU";e={[math]::ROUND($_.CPU  -as [Float],0)}}, ` # CPU,
 Id, Path  | `
Sort-Object "WS(MB)" -Descending | `
Format-Table -AutoSize

Get Logical Disk Information
1
2
3
4
5
6
# Useful logical Disk Info.
Get-WmiObject -Class Win32_LogicalDisk -ComputerName DI21 | Select DeviceID,
    VolumeName, DriveType, ProviderName,FileSystem, Compressed,MediaTYpe,
    @{n="FreeSpaceGB";e={[math]::ROUND($_.FreeSpace / (1024*1024*1024) -as [Float],2)}},
    @{n="SizeGB";e={[math]::ROUND($_.Size / (1024*1024*1024) -as [Float],2)}}, 
    CreationClassName, VolumeDirty, Description, FilesystemSize, ErrorDescription


Get other system Information
Get-WmiObject -Class Win32_LogonSession -ComputerName DI21 

Get-WmiObject -Class Win32_LocalTime -ComputerName . 

Get-WmiObject -Class Win32_Service -ComputerName . | `
    Format-Table -Property Status,Name,DisplayName -AutoSize -Wrap

Get-CimInstance  Win32_Service
Get-CimInstance  Win32_Share
Get-CimInstance  Win32_ShareToDirectory
Get-CimInstance  Win32_StartupCommand
Get-CimInstance  Win32_SystemAccount | SELECT Caption, Name, 
    SID, Status| Format-Table -AutoSize -Wrap
Get-CimInstance  Win32_SystemDevices 
Get-CimInstance  Win32_SystemLoadOrderGroups
Get-CimInstance  Win32_SystemNetworkConnections
Get-CimInstance  Win32_SystemOperatingSystem
Get-CimInstance  Win32_SystemPartitions
Get-CimInstance  Win32_SystemServices
Get-CimInstance  Win32_SystemTimeZone
Get-CimInstance  Win32_SystemUsers
Get-CimInstance  Win32_UserAccount


Referance : https://msdn.microsoft.com/en-us/library/dn792258(v=vs.85).aspx

Wednesday, January 20, 2016

MDX - Parsing a value from one dim to another


Following query pass year 2016 value from [Period] dimension to [Exchange Rate] Dim.

WITH 
member YearX as 
NONEMPTY(
[Period].[Operational Year - Semester - Quarter - Month].[Year].&[2016] ,
[Measures].[Targets - EdoxabanNetSalesTargets]).item(0).Properties("Key")

SELECT  {[Measures].[Exchange Rate]}
on 0, non empty
 (
 [Exchange Rate].[From Currency].[From Currency]
, [Exchange Rate].[To Currency].[To Currency]
)
on 1 from [XXXX Cube]
where STRTOMEMBER( "[Exchange Rate].[Operational Year Key].[Operational Year Key].&[" + 
YearX
+ "]")


Wednesday, December 9, 2015

Powershell, Infile search and file copy between the servers in same domain

Find a keyword within multiple files in a folder.

Cls
$PATH = "D:\DSUK\Daiitchi Sankyo Activity Instance\Warehouse\Data\Staging\Veeva Activity\WE_00DA0000000Ci0mMAC_123\"
$FILES = Get-ChildItem -Path $path  # -Name user.csv*

Foreach ($FILE IN $FILES )
{
Get-Content $PATH$FILE -First 1   | Where-Object { $_ -like '*lm_Presentation_Version_vod*' }

$FILE
}


File copy between the servers in the same domain

Cls
$SourcePath = "\\Server1\PharmaAnalytics\Takeda Instance\Data Input Area"
$DestinationPath  = "\\Server2\PharmaAnalytics\Takeda Instance\Data Input Area"


$Folders = Get-ChildItem -Path $SourcePath  -Name Aver*
FOREACH ($Folder in $Folders)
{
#$DestinationPath + "\"+ $Folder
#Get-ChildItem -Path $SourcePath\$Folder | Where-Object{!($_.PSIsContainer)}
Copy -Path $SourcePath\$Folder\* -Destination $DestinationPath\$Folder | Where-Object{!($_.PSIsContainer)} # Exclude Folders
}



Monday, October 19, 2015

Identify and disconnect / log off remote RDP sessions


Run QWINSTA to extract the RDP session information
  • QWINSTA /SERVER:servername

If the session exists, read the username and session ID.
  • To disconnect user 
    TSDISCON /SERVER:servername sessionID
  • To log-off user / kill the session
    RWINSTA /SERVER:servername sessionID


Ref : http://discoposse.com/2012/10/20/finding-rdp-sessions-on-servers-using-powershell/

Saturday, February 21, 2015

Function to Get Folder Sizes in Powershell

Following Powershell function can be used to get the sizes of given folders.
Folder names hould be given within double quotes seperated by commas.
 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
 function Get-FolderSize {
    param(
        $FolderPaths = $null
        )
    [array]$FolderLists = $null
    foreach ( $FolderPath in $FolderPaths)
    {
        $FolderLists += 
        Get-ChildItem $FolderPath -Force -Recurse | 
        Measure-Object -property Length -Sum |
        select @{n="Path";e={"""" +$FolderPath + """"}} ,
            @{n="Size GB";e={[math]::ROUND($_.Sum / 1GB -as [Float],2)}} , 
            @{n="Size MB";e={[math]::ROUND($_.Sum / 1MB -as [Float],2)}} , 
            @{n="Size KB";e={[math]::ROUND($_.Sum / 1KB -as [Float],2)}}  
    }
    $FolderLists | Format-Table -AutoSize 
 }

The function rutuns the sizes of given folders in kilobyte (KB), megabyte (MB), gigabyte(GB).

Saturday, January 31, 2015

Connect to a SFTP site using Windows Powershell

This post explains how to access  a SFTP server using Windows Powershell commands. There are many file transfer utilities which can be used for this purpose. Here, I use WinSCP  in order to access SFTP server from windows powershell.

1. WinSCP PowerShell Wrapper can be downloaded from https://github.com/dotps1/WinSCP.
2. Import WinSCP Powershell module. $WinSCPFile path variable needs to be set to the WinSCP.psd1 file inside the downloaded folder.

$WinSCPFile = "C:\....\WinSCP-master\WinSCP.psd1"
Import-Module -Name $WinSCPFile -Verbose -ErrorAction Inquire -WarningAction Inquire | Out-null
3. Splat New-WinSCPSessionOptions.
$sessionOptions = @{
HostName = "SFTP-HostName"
UserName = "Username"
Password = "Password"
SshHostKeyFingerprint = "HostKey"
}


To get the SshHostKeyFingerprint, go to \WinSCP-master\NeededAssemblies\ folder and run WinSCP.exe.Then enter HostName, Username, & Password and try connecting to the SFTP site. You will get the SshHostKeyFingerprint when you log in for the first time.

4. Open new WinSCPSession using the splatted parameters.
$session = Open-WinSCPSession -SessionOptions (New-WinSCPSessionOptions @sessionOptions)
 

5. Send a file to SFTP server
Send-WinSCPItem -WinSCPSession $session -LocalPath "C:\localFile.txt" -RemotePath "./remoteDirectory/"
 

6. Get a file from FTP server
Receive-WinSCPItem -WinSCPSession $session -RemotePath "./remoteDirectory/rFile.txt" -LocalPath "C:\localFile.txt"
7. Close SFTP session
Close-WinSCPSession -WinSCPSession $session

Referance: 

https://github.com/dotps1/WinSCP
http://winscp.net/eng/docs/ssh_verifying_the_host_key

Wednesday, December 31, 2014

Working with System date + SSAS + MDX

The purpose of following MDX query is to create the CompleteDataMonthFlag flag which can be used to identify the latest month with complete data. For example, if we receive data with granularity less than a month (i.e. weekly or daily), we could use this flag to identify the latest month with complete data.

WITH 
// Following set is to get the system year and month
SET [System Month - Operational] as 
    StrToMember("[Period].[Operational Year - Quarter - Month].[Month].&[" + Format(now(), 'yyyy') + Format(now(), 'MM')+"]")

// Following set is to get the month with latest data
SET [Latest Data Month - Operational] AS Tail(Nonempty([Period].[Operational Year - Quarter - Month].[Month].members), 1)
//Member [Measures].[LatestDataMonth] AS LatestDataMonth.item(0).name
//Member [Measures].[CurrentMonth] AS CurrentMonth.item(0).name
SET LatestMonth AS
    CASE
    WHEN [Latest Data Month - Operational].item(0)  = [System Month - Operational].item(0)
        THEN [Latest Data Month - Operational].item(0).lag(1)  
    WHEN [Latest Data Month - Operational].item(0)  <> [System Month - Operational].item(0)
        THEN [Latest Data Month - Operational].item(0) 

    END      
MEMBER [Measures].[CompleteDataMonthFlag] as
    IIF ([Period].[Operational Year - Quarter - Month] is LatestMonth.Item(0),1, 0)

SELECT {
[Measures].[CompleteDataMonthFlag]
}
ON 0 ,
NON EMPTY
[Period].[Operational Year - Quarter - Month].[Month].members
ON 1
FROM [Cube Name];


IF the [CompleteDataMonthFlag] needs to be implemented within the SSAS cube as a calculated member, the following script can be used

Create Set CurrentCube.[System Month - Operational] AS 

    StrToMember("[Period].[Operational Year - Quarter - Month].[Month].&[" + Format(now(), 'yyyy') + Format(now(), 'MM')+"]");
Create Set CurrentCube.[Latest Data Month - Operational] AS 

    Tail(Nonempty([Period].[Operational Year - Quarter - Month].[Month].members), 1);
Create Set CurrentCube.LatestMonth AS
    CASE
    WHEN [Latest Data Month - Operational].item(0)  = [System Month - Operational].item(0)
        THEN [Latest Data Month - Operational].item(0).lag(1)

    WHEN [Latest Data Month - Operational].item(0)  <> [System Month - Operational].item(0)
        THEN [Latest Data Month - Operational].item(0)

    END;     
CREATE MEMBER CURRENTCUBE.[Measures].[CompleteDataMonthFlag] as
    IIF ([Period].[Operational Year - Quarter - Month] is LatestMonth.Item(0),1, 0);


Ref: 
http://sqlblog.com/blogs/mosha/archive/2007/05/23/how-to-get-the-today-s-date-in-mdx.aspx
http://sqljoe.wordpress.com/2011/07/22/dynamically-generate-current-year-month-or-date-member-with-mdx/

Monday, December 1, 2014

Backup and Restore Multiple Databases and Cubes

SSAS Cubes
1. List all the cube names in a MS Analysis Server instance.
Open a MDX / DAX query window and run followoing DMVquery.
    SELECT * FROM $system.dbschema_catalogs
    ORDER BY [catalog_name] ASC

2. Backup multiple databases in a MS Analysis Server instance.
Sometimes, database name may not be same as database ID. In that case, DatabaseID needs to be found by going to properties of the database.
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="false" >
   <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
       <Object>
       <DatabaseID>Cube 2</DatabaseID>
       </Object>
       <File>C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Cube 1.abf</File>
       <AllowOverwrite>true</AllowOverwrite>
   </Backup>
   <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
       <Object>
       <DatabaseID>
C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Cube 2</DatabaseID>
       </Object>
       <File>Cube 2.abf</File>
       <AllowOverwrite>true</AllowOverwrite>
   </Backup>
</Batch>

Ref: http://blog.sqltechie.com/2010/01/how-to-backup-multiple-database-using.html

SQLServer Databases
1. Backup all the databases in a SQLServer  instance can be done using following script (you can exclude any database by defining it within the where clause of select statement).

DECLARE @name VARCHAR(50) -- Database name 
DECLARE @path VARCHAR(256) -- Backup files' path
DECLARE @fileName VARCHAR(256) -- Backup files' filename
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = 'D:\Microsoft SQL Server 2005\Analysis Services\MSSQL.2\OLAP\Backup\DatabaseMigration-09-06-2014\' 

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE cursor1 CURSOR FOR 
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','Report Administration','ReportServer','ReportServerTempDB' )

OPEN cursor1  
FETCH NEXT FROM cursor1 INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName 

       FETCH NEXT FROM cursor1 INTO @name  
END  
CLOSE cursor1  
DEALLOCATE cursor1


2. Restore database using following command
Back files need to be copied to MS SQL Server\MSSQL.1\MSSQL\data Folder.
RESTORE DATABASE [AnalyticsPortal]
   FROM DISK = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AnalyticsPortal.BAK'
   WITH MOVE 'AnalyticsPortal' TO 'd:\Microsoft SQL Server\MSSQL.1\MSSQL\data\AnalyticsPortal.mdf',
   MOVE 'AnalyticsPortal_log' TO 'd:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AnalyticsPortal_1.LDF'