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

Types of measures/facts tables

Fact Table Types

Fully Additive Facts: This measure which can be added across all the associated dimensions.
Example: sales amount is a fact which can be summed across different dimensions like customer, geography, date, etc.

Semi-Additive Facts: These are facts which can be added across only few dimensions.
Example: bank balance is a fact which can be summed across the customer dimension but it cannot be added across the date dimension

Non-Additive Facts: These are facts which cannot be added across any of the dimensions in the cube.
Example: profit margin is a fact which cannot be added across any of the dimensions. we can not add against product dimension.

Derived Facts: Derived facts are the facts which are calculated from one or more facts,
For example: profit margin.Which is calculated when needed

Factless Facts: Fact table will not have a measure they will have only the key values(Foreign key) refered to the dimension are called factless fact table.

Types of measures/facts tables 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

Get Year and Month in two digit format in SQL SERVER

Get Year Month two digit format SQL SERVER

Get Year Month two digit format

Need to convert the date to get the month value in two digit format and then we need to concatenate to display as a single value.

select convert(nvarchar,YEAR(GETDATE()))  + SUBSTRING(CONVERT(nvarchar,getdate(), 112),5,2)
Get Year and Month in two digit format in SQL SERVER was last modified: October 1st, 2017 by Justin Antony

Passing dynamic date value based on day of the week to SSRS report.

Dynamic date

IN SSRS We can pass the last week Friday and current week Thursday dynamically as input parameter. You can also change the day of the week by changing the DATEADD value in the following query.

 SELECT DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
SELECT DATEADD(DAY, 3, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
Passing dynamic date value based on day of the week to SSRS report. was last modified: October 1st, 2017 by Justin Antony