Skip to content

Instantly share code, notes, and snippets.

@mlongoria
mlongoria / Color 1.M
Created Aug 16, 2020
Color Contrast Math
View Color 1.M
let
//Get values 0 - 255
Source = Values,
//Call that column R for Red
#"R Dec" = Table.RenameColumns(Source,{{"Column1", "R Dec"}}),
//Crossjoin to Values to get Green values 0 - 255
#"G Dec" = Table.AddColumn(#"R Dec", "Custom", each Values),
#"Expanded G Dec" = Table.ExpandTableColumn(#"G Dec", "Custom", {"Column1"}, {"G Dec"}),
//Crossjoin to Values to get Blue values 0 - 255
#"B Dec" = Table.AddColumn(#"Expanded G Dec", "B", each Values),
@mlongoria
mlongoria / SQLDWTableSwapWithClassifications
Created May 29, 2019
Stored procedure to swap tables via RENAME and DROP TABLE and transfer any sensitivity classifications from the original table to the new table
View SQLDWTableSwapWithClassifications
CREATE PROC SwapWithMetadata
@SrcSchema NVARCHAR(128),
@SrcTable NVARCHAR(128),
@DestSchema NVARCHAR(128),
@DestTable NVARCHAR(128),
@TransferMetadata BIT,
@DropOldTable BIT
AS
BEGIN
View TF Dynamics CRM Source Biml
<!-- This goes in your data flow as the source -->
<CustomComponent Name="TF Dynamics 365 <#=table.Name#>" ComponentTypeName="PragmaticWorks.TaskFactory.DynamicsSource" Version="1" ContactInfo="Dynamics Source;Pragmatic Works, Inc; Task Factory (c) 2009 - 2016 Pragmatic Works, Inc; http://www.pragmaticworks.com;support@pragmaticworks.com" UsesDispositions="true">
<Annotations>
<Annotation AnnotationType="Description">Extract data from Microsoft Dynamics CRM.</Annotation>
</Annotations>
<DataflowOverrides>
<OutputPath OutputPathName="Dynamics Source Output">
<!-- Iterate through the columns (I'm passing in table and column from another file), ignoring any audit columns you may have added to your table. Set the Error Row Disposition and Truncation Row Disposition. -->
<# foreach (var column in table.Columns.Where(c => !c.Name.Equals("AuditETLLogID") && !c.Name.Equals("AuditIn
@mlongoria
mlongoria / PL_Copy_MySourceDBToADLS.biml
Last active Mar 10, 2017
Biml ADF Pipeline Generation
View PL_Copy_MySourceDBToADLS.biml
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Text" #>
<#@ property name="targetTables" type="DataView"#>
<#@ property name="frequency" type="string"#>
<#@ property name="scope" type="string"#>
{
"$schema": "http://datafactories.schema.management.azure.com/schemas/2015-09-01/Microsoft.DataFactory.Pipeline.json",
"name": "PL_Copy_MySourceDBToADLS_<#=frequency#>_<#=scope#>",
"properties": {
"description": "<#=frequency#> <#=scope#> copies of data from Source db to the data lake.",
@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";
@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",
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 / 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"
}
]
}
}
@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 / 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 (
You can’t perform that action at this time.