Types of Dimension Tables in SSAS

Dimension Tables Types
Dimension table contains the master data information. A dimension table has a primary key column that uniquely identifies each dimension record. The dimension table is associated with a fact table using the primary key.

confirmed dimension — The value will be refered by more than one dimension table
junk dimension — Combined values of transaction
degenerate attribute — If a dimension data kept on a fact table for reference
Role Playing Dimension — Date dimension which act as a orderdate dimension and purchasedate dimension.

Types of Dimension Tables in SSAS was last modified: October 5th, 2017 by Justin Antony

How Record Set object used in SSIS ?

Record Set object SSIS
Record Set object is used to store the values temporary to an object variable and use that object variable in the ETL

EG :

Get the information form flat file

Store the information in to the Object variable using the Record set object

Get the value form object variable when needed.

How Record Set object used in SSIS ? was last modified: October 1st, 2017 by Justin Antony

What is a SSIS breakpoint and how to configure it?

SSIS breakpoint
To troubleshoot an SSIS Package we use the breakpoints.
Right click on any task in control flow and then select Edit Breakpoint, This will display a Breakpoint window. choose when the execution to be paused(OnPreExecute, OnPostExecute, OnError events). This will be helpful to see the values of a variable at runtime using the output window.

What is a SSIS breakpoint and how to configure it? was last modified: October 1st, 2017 by Justin Antony

Find SQL Server Edition Bit Version Special Pack and Version Number Details using Query

Find SQL Server Edition using Query
You can find using the following Query

SELECT SERVERPROPERTY('edition') AS SQLSERVER_BIT_VERSION,
SERVERPROPERTY('productlevel') AS SPECIAL_PACK
,SERVERPROPERTY('productversion') AS VERSION_NUMBER
OR
SELECT @@VERSION

 

Find SQL Server Edition Bit Version Special Pack and Version Number Details using Query was last modified: October 1st, 2017 by Justin Antony

Slicer control on a multiple data source pivot excel – 1004 Runtime Error in VBA Script.

Slicer control multiple data source pivot excel VBA Script
Using the below code you can control the Slicer_Name2 from Slicer_Name1 belongs to different data source of a pivot Excel.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim sc1 As SlicerCacheLevel
Dim sc2 As SlicerCacheLevel
Dim sc3 As SlicerCache
Dim sc4 As SlicerCache
Dim ST1 As SlicerItem
Dim ST2 As SlicerItem
Dim sc As SlicerCache
Dim si As SlicerItem
Dim list As Variant
Application.ScreenUpdating = False
Application.EnableEvents = False
Set sc3 = ActiveWorkbook.SlicerCaches("Slicer_Name1")
Set sc4 = ActiveWorkbook.SlicerCaches("Slicer_Name2")
For Each sc1 In ThisWorkbook.SlicerCaches("Slicer_Name1").SlicerCacheLevels
For Each ST1 In sc1.SlicerItems
For Each sc2 In ThisWorkbook.SlicerCaches("Slicer_Name2").SlicerCacheLevels
For Each ST2 In sc2.SlicerItems
If ST1.Selected = True And ST1.Value = ST2.Value Then
If IsEmpty(list) Then
list = Array(ST2.Name)
Else
ReDim Preserve list(UBound(list) + 1)
list(UBound(list)) = ST2.Name
End If
sc4.VisibleSlicerItemsList = list                        
Exit For
End If
Next
Next
Next        
Next     
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Slicer control on a multiple data source pivot excel – 1004 Runtime Error in VBA Script. was last modified: October 1st, 2017 by Justin Antony

Refresh PowerPivot excel using SSIS

Refresh PowerPivot excel using SSIS

Install the SSIS_EXCEL_REFRESH.EXE in your ETL System and then you will find that component displaying in the SSIS Toolbox.

  1. Configure the EXCEL Refresh Tool
  2. Provide the Path where the Excel resides
  3. Set the Timeout for Refresh
  4. Run the ETL and check the Excel being refreshed.

You can check that by seeing the excel last modified date gets updated.

 

Refresh PowerPivot excel using SSIS was last modified: October 1st, 2017 by Justin Antony