Tuesday, July 24, 2018

Document SSAS Cube Database ( Data Dictionary )

Simple script to generate SSAS Cube Documentation.
This script generates three separate CSV files : DSV, dimensions and attributes.
Can be further extended to document Measures, Perspectives, Roles or Mining Structures.

  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
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
cls

##########################################################

# Created By : Amit Chauhan

# Created On : 20170215

# Purpose : List Cube dimension and attributes with source query for cube documentation

# Parameters : None

# Test Stub : None

##########################################################

Function Read-HostSpecial {

[cmdletbinding(DefaultParameterSetName="_All")]

Param(

[Parameter(Position = 0,Mandatory,HelpMessage = "Enter prompt text.")]

[Alias("message")]

[ValidateNotNullorEmpty()]

[string]$Prompt,

[Alias("foregroundcolor","fg")]

[consolecolor]$PromptColor,

[string]$Title,

[Parameter(ParameterSetName = "SecureString")]

[switch]$AsSecureString,

[Parameter(ParameterSetName = "NotNull")]

[switch]$ValidateNotNull,

[Parameter(ParameterSetName = "Range")]

[ValidateNotNullorEmpty()]

[int[]]$ValidateRange,

[Parameter(ParameterSetName = "Pattern")]

[ValidateNotNullorEmpty()]

[regex]$ValidatePattern,

[Parameter(ParameterSetName = "Set")]

[ValidateNotNullorEmpty()]

[string[]]$ValidateSet

)

Write-Verbose "Starting: $($MyInvocation.Mycommand)"

Write-Verbose "Parameter set = $($PSCmdlet.ParameterSetName)"

Write-Verbose "Bound parameters $($PSBoundParameters | Out-String)"

#combine the Title (if specified) and prompt

$Text = @"

$(if ($Title) {

"$Title'n$("-" * $Title.Length)"

})

$Prompt :

"@

#create a hashtable of parameters to splat to Write-Host

$paramHash = @{

NoNewLine = $True

Object = $Text

}

if ($PromptColor) {

$paramHash.Add("Foregroundcolor",$PromptColor)

}

#display the prompt

Write-Host @paramhash

#get the value

if ($AsSecureString) {

$r = $host.ui.ReadLineAsSecureString()

}

else {

#read console input

$r = $host.ui.ReadLine()

}

#assume the input is valid unless proved otherwise

$Valid = $True

#run validation if necessary

if ($ValidateNotNull) {

Write-Verbose "Validating for null or empty"

if($r.length -eq 0 -OR $r -notmatch "\S" -OR $r -eq $Null) {

$Valid = $False

Write-Error "Validation test for not null or empty failed."

}

}

elseif ($ValidatePattern) {

Write-Verbose "Validating for pattern $($validatepattern.ToString())"

If ($r -notmatch $ValidatePattern) {

$Valid = $False

Write-Error "Validation test for the specified pattern failed."

}

}

elseif ($ValidateRange) {

Write-Verbose "Validating for range $($ValidateRange[0])..$($ValidateRange[1]) "

if ( -NOT ([int]$r -ge $ValidateRange[0] -AND [int]$r -le $ValidateRange[1])) {

$Valid = $False

Write-Error "Validation test for the specified range ($($ValidateRange[0])..$($ValidateRange[1])) failed."

}

else {

#convert to an integer

[int]$r = $r

}

}

elseif ($ValidateSet) {

Write-Verbose "Validating for set $($validateset -join ",")"

if ($ValidateSet -notcontains $r) {

$Valid = $False

Write-Error "Validation test for set $($validateset -join ",") failed."

}

}

If ($Valid) {

Write-Verbose "Writing result to the pipeline"

#any necessary validation passed

$r

}

Write-Verbose "Ending: $($MyInvocation.Mycommand)"

} #end function

#define an alias

Set-Alias -Name rhs -Value Read-HostSpecial

$serverName = ""

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-Null

$server = new-Object Microsoft.AnalysisServices.Server

do

{

$Error.Clear()

$serverName=Read-HostSpecial "Enter SSAS Server Name with Instance'r'ne.g. SERVERNAME\INSTANCENAME or 10.152.1.10\INSTANCENAME" -PromptColor Green -ValidateNotNull

$server.Connect($serverName)

if($Error){$done=1}

}

while($done -eq 0)

$server.Databases | Format-Wide

$dbname=Read-HostSpecial "'r'nEnter SSAS Database Name to be exported from above list" -PromptColor Green -ValidateNotNull

# try{

$CubeList = @()

$DSVList = @()

$DimList = @()

$AttributeList = @()

ForEach($db in $server.Databases)

{

if( $db.Name.ToLower() -eq $dbname.ToLower() )

{

Write-Host "Matching cube found : " $db.Name

foreach ($cube in $db.Cubes)

{

"DSV found : 'r'n "

$prevDSVName=""

foreach($dsvSchemaTable in $cube.DataSourceView.Schema.Tables)

{

$dsvname=$cube.DataSourceView.Name

if($dsvname -ne $prevdsvname) {" " + $dsvname ; $prevdsvname=$dsvname}

#$dsv = New-Item PSObject

foreach($p in $dsvSchemaTable.ExtendedProperties)

{

$dsvTemp= New-Object PSObject

$dsvTemp | add-member -membertype NoteProperty -name "Type" -value "DSVTable"

$dsvTemp | add-member -membertype NoteProperty -name "Cube" -value $cube.Name

$dsvTemp | add-member -membertype NoteProperty -name "DSVName" -value $cube.DataSource.Name

$dsvTemp | add-member -membertype NoteProperty -name "DSVTable" -value $dsvSchemaTable.TableName

$dsvTemp | add-member -membertype NoteProperty -name "SourceSchemaQuery" -value $p.QueryDefinition

$DSVList+=$dsvTemp

}

}

#Write-Host "Dimension","DimensionID","DimensionName","SourceTable","AttributeList","HierarchyList","AttributeCount","HeirarchyCount"

foreach ($dim in $cube.Dimensions)

{

$attListName=""

$hieListName=""

foreach($a in $dim.Attributes)

{

$attTemp= New-Object PSObject

$attTemp | add-member -membertype NoteProperty -name "Type" -value "Attribute"

$attTemp | add-member -membertype NoteProperty -name "Cube" -value $cube.Name

$attTemp | add-member -membertype NoteProperty -name "ParentDimension" -value $dim.DimensionID

$attTemp | add-member -membertype NoteProperty -name "AttributeID" -value $a.AttributeID

$attTemp | add-member -membertype NoteProperty -name "AttributeIDName" -value $a.Attribute.name

$attTemp | add-member -membertype NoteProperty -name "Usage" -value $a.Attribute.Usage

$attTemp | add-member -membertype NoteProperty -name "DerivedFromTableId" -value $a.Attribute.DerivedFromTableId

$attTemp | add-member -membertype NoteProperty -name "DerivedFromColumnId" -value $a.Attribute.DerivedFromColumnId

$attTemp | add-member -membertype NoteProperty -name "NameColumn" -value $a.Attribute.NameColumn

$keyColumnList=""

foreach($attributeKeyColumn in $a.Attribute.KeyColumns)

{

$keyColumnList+= $attributeKeyColumn.Source.TableID +"."+ $attributeKeyColumn.Source.ColumnID +","

}

$attTemp | add-member -membertype NoteProperty -name "KeyColumn" -value $keyColumnList

$attTemp | add-member -membertype NoteProperty -name "ValueColumn" -value $a.Attribute.ValueColumn

$AttributeList+=$attTemp

$attListName+=$a.Attribute.name +","

} # end of attribute

foreach($dsvSchemaTable in $dim.Hierarchies)

{

#"Hierarchy," + $dim.DimensionID +","+ $dsvSchemaTable.DataSet. +","+$a.Attribute.name +","+ $a.Attribute.DerivedFromTableId +","+ $a.Attribute.DerivedFromColumnId +$a.Attribute.NameColumn +","+$a.Attribute.KeyColumns

$hieListName+=$dsvSchemaTable.Hierarchy.ID +","

}

$dimTemp= New-Object PSObject

$dimTemp | add-member -membertype NoteProperty -name "Type" -value "Dimension"

$dimTemp | add-member -membertype NoteProperty -name "Parent" -value $dim.Parent

$dimTemp | add-member -membertype NoteProperty -name "DimensionID" -value $dim.Dimension.ID

$dimTemp | add-member -membertype NoteProperty -name "DimensionName" -value $dim.Dimension.Name

$dimTemp | add-member -membertype NoteProperty -name "DimensionDesc" -value $dim.Dimension.Description

$dimTemp | add-member -membertype NoteProperty -name "DimensionKeyAttribute" -value $dim.Dimension.KeyAttribute

$dimTemp | add-member -membertype NoteProperty -name "DimensionDSV" -value $dim.Dimension.DataSourceView.ID

$dimTemp | add-member -membertype NoteProperty -name "DimensionDS" -value $dim.Dimension.DataSourceView.DataSource

$dsvname=$dim.Dimension.Annotations.Item(0).Value.ddscontrol.layoutobject.ddsxmlobj.property.value

$dimTemp | add-member -membertype NoteProperty -name "DimensionSourceTable" -value $dsvname

$dimTemp | add-member -membertype NoteProperty -name "AttributeCount" -value $dim.Attributes.Count

$dimTemp | add-member -membertype NoteProperty -name "HierarchyCount" -value $dim.Hierarchies.Count

$dimTemp | add-member -membertype NoteProperty -name "AttributeList" -value $attListName

$dimTemp | add-member -membertype NoteProperty -name "HierarchyList" -value $hieListName

$dsvquery = ($DSVList.Where{$_.DSVTable -eq $dsvname }) | Select-Object -ExpandProperty "SourceSchemaQuery"

$dimtemp | add-member -membertype NoteProperty -name "SourceQuery" -value $dsvquery

$DimList+= $dimTemp

$dimTemp = $null

$dsvName =""

} # end of dimension

} # end of cube

} ##endif dbname match

} #end of foreach database

#} #end of try

#catch { "Error occurred :"+ $Error }

do

{

$Error.Clear()

$inputfile=Read-HostSpecial "Files ready!'r'nPlease select output path " -PromptColor Green -ValidateNotNull

}

while( (Test-Path $inputfile) -eq $false )

try{

$filename=$inputfile+"\"+ $cube.Name + "-DSV.csv"

If (Test-Path $filename){ Remove-Item $filename }

$DSVList | Export-Csv $filename -NoTypeInformation -NoClobber -Force

Write-Information "Exported $filename"

}

catch{ "Failed export : "+ $Error.ToString(); }

if($Error.Count -eq 0){ "Exported : $filename"; $Error.Clear();}

try{

$filename=$inputfile +"\"+ $cube.Name + "-DimensionList.csv"

If (Test-Path $filename){ Remove-Item $filename }

$DimList | Export-Csv $filename -NoTypeInformation -NoClobber -Force

Write-Information "Exported $filename"

}

catch{ "Failed export : "+ $Error.ToString(); }

if($Error.Count -eq 0){ "Exported : $filename"; $Error.Clear();}

try{

$filename=$inputfile+"\"+ $cube.Name + "-AttributeList.csv"

If (Test-Path $filename){ Remove-Item $filename }

$AttributeList | Export-Csv $filename -NoTypeInformation -NoClobber -Force

Write-Information "Exported $filename"

}

catch{ "Failed export : "+ $Error.ToString(); }

if($Error.Count -eq 0){ "Exported : $filename"; $Error.Clear();}

"Press any key to exit."

cmd /c pause | out-null

Export SSAS diagram as single image

Visual studio doesn't give option to SSAS Database / Cube diagram in JPG, until micrsoft come with their own option to export it to image - here is the workaround.

1. Export to PDF
2. PDF to JPGs
3, Crop jpgs (Irfanview Portable)
4. Create HTML page to view all images in table. (assuming diagram name is mydiagram)
code

<html> <head> <style> td, tr, img { padding: 0px; margin: 0px; border: none; } td { width:1694;height:2149;} table { border-collapse: collapse; background-color:red; border-spacing: 0px; } </style> <script language="javascript" type="text/javascript" > function padDigits(number, digits) { return Array(Math.max(digits - String(number).length + 1, 0)).join(0) + number; } document.write('\r\n\t<table'); for (var i = 0; i < 10; i++) { document.write('\r\n\t<tr>'); for (var j = 0; j < 6; j++) { document.write('\r\n\t\t<td style="border: 0px none black "><img src="mydiagram-' + padDigits(((i * 6) + j) + 1, 2) + '.jpg" class="cropimg" /> </td>'); } document.write('\r\n\t</tr>'); } document.write('\r\n\t</table>'); </script> </head> <body> </body> </html>

5. Export web page with chrome extension Full Page Screen Capture offered by mrcoles.com ( Grant local page file permission)

Thursday, July 19, 2018

get wallmart page search result in csv clipboard

If you are running chrome copy below text
press F12 and select console
press Ctrl+L
copy and paste below text (available on https://jsfiddle.net/52kjy9zg/1/ )

and results are in your clipboard


 var p1 = $x('//*[@id="searchProductResult"]/ul/li');
 var result = "";
 for (i = 0; i < p1.length; i++) {
     var name = "\"" + $x('//*[@id="searchProductResult"]/ul/li[' + i + ']/div/div/div/div/span/a/span')
         .map(e => e.textContent)
         .join('') + "\"";
     var price = "\"" + $x('//*[@id="searchProductResult"]/ul/li[' + i + ']/div/div/div/div/span/div/div/div/span/span')
         .map(e => e.textContent)
         .join('') + "\"";
     var originalprice = "\"" + $x('//*[@id="searchProductResult"]/ul/li[' + i + ']/div/div/div/div/span/div/span/span/span/span/span')
         .map(e => e.textContent)
         .join('') + "\"";
     result += name + "," + price + "," + originalprice + "\r\n";
 }
 var copy = function(e) {
     e.preventDefault();
     console.log('copy');
     var text = "blabla"
     if (e.clipboardData) {
         e.clipboardData.setData('text/plain', result);
     } else if (window.clipboardData) {
         window.clipboardData.setData('Text', result);
     }
 }
 window.addEventListener('copy', copy);
 document.execCommand('copy');
 window.removeEventListener('copy', copy);

Wednesday, December 13, 2017

Merge Same Name Worksheet to single worksheet

It so happens, we have several excel that has multiple worksheet following same format.

You are in hurry and want to just merge them in to one excel.


Here is what you might do manually.

Select a folder that has all excel that needs to be merged.
Target output excel file will be "\Output\Merged.xlsx"
For each excel
- For each worksheet
- Copy data
- Lookup in merged excel
- - if same worksheet exists then paste at end
- - else create new worksheet and paste
- repeat it.(until you are exhausted)


Below is the VBA macro that exactly what it does for you.

Create new blank worksheet/excel and run it.
If you new to VBA/Macro and don't know where to place below code and run it only using your excel, click here. (at step 4 paste below code and press F8 and then F5, which will prompt source folder)

This is my first blog entry here. 
Kindly put thanks note if you find it useful.

Sub MergeSameNamedWorkSheets()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim masterwbk As Workbook
    Dim childwbk As Workbook
    Dim wbk1 As Workbook
    Dim filename As String
    Dim Path As String
    Dim NewWkbk As Workbook
    NewWkbk = Workbooks.Add
    Dim mywkb As Workbook

    mywkb = ThisWorkbook
    ThisWorkbook.Sheets(1).Copy Before:=NewWkbk.Sheets(1)
    Dim fldr As FileDialog
    Dim sItem As String

    fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select Source Folder"
        .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem

    Path = GetFolder & "\" 'CHANGE PATH
    Dim fdObj As Object
    fdObj = CreateObject("Scripting.FileSystemObject")
    If fdObj.FolderExists(Path & "Output") = False Then
        fdObj.CreateFolder(Path & "Output")
    End If
    NewWkbk.SaveAs Path & "Output\Merged.xlsx"
    fldr = Nothing



    filename = Dir(Path & "*.xlsx")

    Do While Len(filename) > 0


        Application.ScreenUpdating = True
        mywkb.Activate()
        ActiveSheet.Cells(ActiveCell.Row + 1, 1).Select()
        ActiveCell.value = Path & filename
        ActiveSheet.Cells(ActiveCell.Row, 2).Select()
        ActiveCell.value = "Started"
        Application.ScreenUpdating = False
        masterwbk = Workbooks.Open(Path & "output\Merged.xlsx")
        masterwbk.Activate()

        Dim masterws() As Object

        Dim str As Object
        For I = 0 To masterwbk.Worksheets.Count - 1
            str = masterwbk.Worksheets(I + 1)
    ReDim Preserve masterws(I + 1) As Variant
            masterws(I) = str.Name
        Next I

        childwbk = Workbooks.Open(Path & filename)
        childwbk.Activate()

        For I = 1 To childwbk.Worksheets.Count - 1


       Dim ws As Worksheet
       Sheets(I).Select()
       ws = childwbk.ActiveSheet
       Dim childwsname As String
       childwsname = ws.Name
        If LCase(Left(childwsname, 5)) = "sheet" Then GoTo continue

            If Not containsvalue(masterws, childwsname) Then

                ws = masterwbk.Sheets.Add(After:= _
                         masterwbk.Sheets(masterwbk.Sheets.Count))
                ws.Name = childwsname
                Dim ubmasterws As Integer
                ubmasterws = UBound(masterws)
                ReDim Preserve masterws(ubmasterws + 1) As Variant
                masterws(ubmasterws) = childwsname
            End If

            Range("A1").Select()
            Range(Selection, Selection.End(xlToRight)).Select()
            Range(Selection, Selection.End(xlDown)).Select()
            Selection.Copy()

            'Set wbk1 = ThisWorkbook
            Windows("Merged.xlsx").Activate()
            masterwbk.Activate()
            Sheets(childwsname).Select()
            Cells.SpecialCells(xlCellTypeLastCell).Select()
            ActiveSheet.Cells(ActiveCell.Row, 1).Select()
            ActiveSheet.Cells(ActiveCell.Row + 1, ActiveCell.Column).Select()

            If (Len(Trim(Selection.Text)) = 0) Then
                ActiveSheet.Paste()
                'Application.ScreenUpdating = True
                mywkb.Activate()
                ActiveSheet.Cells(ActiveCell.Row, 3).Select()
                ActiveCell.value = CInt(ActiveCell.value) + 1
                Application.ScreenUpdating = False
            End If
            Windows(filename).Activate()
            Continue For
        Next

        Range("A1").Select()
        Selection.Copy()
        ActiveSheet.Paste()
        Application.CutCopyMode = False

        childwbk.Close False
        masterwbk.Close True

        Application.ScreenUpdating = True
        mywkb.Activate()
        ActiveSheet.Cells(ActiveCell.Row, 4).Select()
        ActiveCell.value = "Completed"

        Application.ScreenUpdating = False


        filename = Dir
    Loop
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "All source files are merged and stored at " & Path & "Output\Merged.xlsx"

End Sub

Function containsvalue(values As Object, value As String) As Boolean
    containsvalue = False

    For I = 0 To UBound(values)
        If values(I) = value Then
            containsvalue = True
            Exit For
        End If
        If values(I) = vbEmpty Then
            Exit For
        End If
    Next I

End Function