Pages

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