This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<#* 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"; | |
#> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<#*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" #> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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)) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Lost Customers := | |
IF ( | |
NOT ( | |
MIN ( 'Date'[Full Date] ) | |
> CALCULATE ( MAX ( Sales[Invoice Date] ), ALL ( Sales ) ) | |
), | |
COUNTROWS ( | |
FILTER ( | |
ADDCOLUMNS ( | |
FILTER ( |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"name": "Date", | |
"dataCategory": "Time", | |
"columns": [ | |
{ | |
"type": "calculatedTableColumn", | |
"name": "Date", | |
"dataType": "dateTime", | |
"isNameInferred": true, | |
"isDataTypeInferred": true, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"refresh": { | |
"type": "full", | |
"objects": [ | |
{ | |
"database": "MyTabularModel" | |
} | |
] | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<#@ 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", |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<#@ 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"; |
OlderNewer