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

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

mlongoria

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

mlongoria

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

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

--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

<#*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

<#* 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";
#>