Isura's Blog: 2016

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

Using DMV Queries to get Cube Metadata.

Referenceshttps://msdn.microsoft.com/en-us/library/hh230820(v=sql.110).aspxhttps://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/
http://blogs.microsoft.co.il/yanivmor/2010/01/27/dmvs-for-analysis-services/
https://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/

Thursday, February 18, 2016

PowerShell - Get System information

Get PowerShell Information
$Host

Get Computer System Information

# 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

# 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

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

# 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 Installed Program Information

Get-WmiObject -Class Win32_Product -Computer . | ` 
    sort Name | Format-Table  Name, Version, Vendor

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
+ "]")

Pages