Skip to content

Instantly share code, notes, and snippets.

@mlongoria
mlongoria / TextNuggetReplaceNames.biml
Last active Jun 30, 2016
Demonstration of using text nuggets in Biml
View TextNuggetReplaceNames.biml
<#* 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
mlongoria / Dim1Caller.biml
Last active Jul 1, 2016
Caller file for Type 1 SCD design pattern.
View Dim1Caller.biml
<#*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
mlongoria / RemoveLineBreaks.sql
Created Aug 6, 2016
Dynamically creates a select query for all fields in a table that replaces line breaks in string fields with 4 spaces
View RemoveLineBreaks.sql
--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
mlongoria / CreateDateAzureSQLDW.sql
Last active Sep 23, 2020
Creates a calendar table in Azure SQL Data Warehouse
View CreateDateAzureSQLDW.sql
--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
mlongoria / DAXLostCustomers.txt
Created Aug 8, 2016
Shows the calculation of lost customers based upon sales fact using DAX for SSAS Tabular
View DAXLostCustomers.txt
Lost Customers :=
IF (
NOT (
MIN ( 'Date'[Full Date] )
> CALCULATE ( MAX ( Sales[Invoice Date] ), ALL ( Sales ) )
),
COUNTROWS (
FILTER (
ADDCOLUMNS (
FILTER (
@mlongoria
mlongoria / SSASTabDateDim.JSON
Last active Sep 2, 2016
Creates a Date dim with fiscal calendar all based off of a calculated table. Assumes months are calendar. Fiscal Year start determined by value in [Fiscal Year Month Begin]
View SSASTabDateDim.JSON
{
"name": "Date",
"dataCategory": "Time",
"columns": [
{
"type": "calculatedTableColumn",
"name": "Date",
"dataType": "dateTime",
"isNameInferred": true,
"isDataTypeInferred": true,
@mlongoria
mlongoria / SSASProcessFullTMSL.xmla
Created Sep 19, 2016
Does a Process Full for a 2016+ Tabular SSAS model using TMSL
View SSASProcessFullTMSL.xmla
{
"refresh": {
"type": "full",
"objects": [
{
"database": "MyTabularModel"
}
]
}
}
View TabularModelDMVsForDocumentation
//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
mlongoria / 1_DS_OnPremSQL_MySourceDB_Schema_Table.biml
Last active Mar 3, 2017
BimlScript that is used with an Excel spreadsheet to define datasets and copy pipelines in Azure Data Factory for SQL to ADLS. Needed: List of tables with schema, frequency, indicator of incremental or full load, and field used for window if incremental load
View 1_DS_OnPremSQL_MySourceDB_Schema_Table.biml
<#@ 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
mlongoria / Biml ADF Dataset Generation Snippet.txt
Last active Mar 10, 2017
A file that generates Azure Data Factory datasets and pipelines via Biml
View Biml ADF Dataset Generation Snippet.txt
<#@ 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";