mlongoria

<#* The variables below are control nuggets. 
They will be discussed later but are needed for ths example.*#>
<# 
var PackageName = "Stage_AW_SalesReason";
var SourceTable = "SalesReason";
var SourceSchema = "Sales";
var DestinationTable = "SalesReason";
var DestinationSchema = "Staging";
#>

mlongoria

<#*This file retrieves variable values from a database 
and passes them to another Biml file file that contains 
the design pattern for a type 1 SCD*#>

<#*The items directly below this comment are directives*#>
<#@ template language="C#" hostspecific="true" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>

mlongoria

--Change @Tablename on Line3 and run for each table
DECLARE @TableName varchar(100); 
Set @TableName = 'Goods'
DECLARE @ColumnList	NVARCHAR(MAX);
SET @ColumnList ='';
Declare @sqlselect nvarchar(max); 

SELECT	@ColumnList	 = @ColumnList + ', ' +
CASE WHEN data_type in ('char','varchar','nchar','nvarchar') THEN 'REPLACE(REPLACE('+ CAST(COLUMN_NAME AS VARCHAR(128)) +', CHAR(13),  char(32) + char(32) + char(32) + char(32)), CHAR(10),  char(32) + char(32) + char(32) + char(32))' + CAST(COLUMN_NAME AS VARCHAR(128)) 
WHEN data_type  in ('text','ntext') THEN 'REPLACE(REPLACE(Cast([' + CAST(COLUMN_NAME AS VARCHAR(128)) + '] as varchar(8000)), CHAR(13),  char(32) + char(32) + char(32) + char(32)), CHAR(10),  char(32) + char(32) + char(32) + char(32)) ' + CAST(COLUMN_NAME AS VARCHAR(128))  

mlongoria

--Creates a table called RPT.Calendar. Change the table name on line 69. Change date range on line 2.
DECLARE @StartDate DATE = '20100101', @NumberOfYears INT = 30;

-- prevent set or regional settings from interfering with 
-- interpretation of dates / literals

CREATE TABLE #dimdate
(
  [date]       DATE,  
  [day]        tinyint,

mlongoria

Lost Customers :=
 IF (
     NOT (
         MIN ( 'Date'[Full Date] )
             > CALCULATE ( MAX ( Sales[Invoice Date] ), ALL ( Sales ) )
     ),
     COUNTROWS (
         FILTER (
             ADDCOLUMNS (
                 FILTER (

mlongoria

  {
        "name": "Date",
        "dataCategory": "Time",
        "columns": [
          {
            "type": "calculatedTableColumn",
            "name": "Date",
            "dataType": "dateTime",
            "isNameInferred": true,
            "isDataTypeInferred": true,

mlongoria

{
  "refresh": {
    "type": "full",
    "objects": [
      {
        "database": "MyTabularModel"
      }
    ]
  }
}

mlongoria

//list available DMVs
Select * from $SYSTEM.DBSCHEMA_TABLES where table_type = 'Schema' order by table_name

//Useful DMVs for 2016 SSAS Tabular Models
Select * from $SYSTEM.TMSCHEMA_ATTRIBUTE_HIERARCHY_STORAGES //distinct data count for each column
Select * from $SYSTEM.TMSCHEMA_ATTRIBUTE_HIERARCHIES  //ties hierarchy id to column
SELECT * from $SYSTEM.TMSCHEMA_COLUMN_STORAGES //has order by column, row count is inaccurate
Select * from $SYSTEM.TMSCHEMA_COLUMNS //column name, ID for table, data type, category, hidden, iskey, isunique, is nullable, summarize by, expression for calc columns, hierarchy id, refresh time, modify time. source provider type, display folder
SELECT * from $SYSTEM.TMSCHEMA_DATA_SOURCES //connection string, account, impersonation mode, name
Select * from $SYSTEM.TMSCHEMA_HIERARCHIES //hierarchy name, display folder

mlongoria

<#@ property name="schema" type="string" #>
<#@ property name="table" type="string" #>
<#@ property name="frequency" type = "string" #>

{
    "$schema": "http://datafactories.schema.management.azure.com/schemas/2015-09-01/Microsoft.DataFactory.Table.json",
    "name": "DS_OnPremSQL_MySourceDB_<#=schema#>_<#=table#>",
    "properties": {
        "type": "SqlServerTable",
        "linkedServiceName": "LS_OnPremSQL_MySourceDB",

mlongoria

<#@ template tier="10" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Text" #>
<#@ code file="BGHelper.cs" #>
<#@ import namespace="BGHelper" #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
</Biml>
<#
    string mdFilePath = "C:\\Users\\admin\\Source\\Workspaces\\SH Data Warehouse\\metadata";