ADLA Powershell upload files

Import-AzureRmDataLakeStoreItem
-Account wingspandevadla
-Destination /output/temp/srccust.tsv
-Path C:\Users\nitin24g\Downloads\CustomerDIM.csv

Submit ADLA job in ADF with Parameters

Linked Service:
 
AzureDataLakeAnalyticsLinkedService:
{
    "name": "AzureDataLakeAnalyticsLinkedService",
    "properties": {
        "hubName": "devadf_hub",
        "type": "AzureDataLakeAnalytics",
        "typeProperties": {
            "accountName": "devadla",
            "dataLakeAnalyticsUri": "azuredatalakeanalytics.net",
            "authorization": "**********",
            "sessionId": "**********",
            "subscriptionId": "42195f5d-f64b-45d3-a497-b4f61559c464",
            "resourceGroupName": "RGdev"
        }
    }
}

Source-BlobStorageLS:
{
    "name": "Source-BlobStorageLS",
    "properties": {
        "hubName": "devadf_hub",
        "type": "AzureStorage",
        "typeProperties": {
            "connectionString": "DefaultEndpointsProtocol=https;AccountName=blobAccount;AccountKey=**********"
        }
    }
}

Destination-DataLakeStore : 
{
    "name": "Destination-DataLakeStore",
    "properties": {
        "hubName": "devadf_hub",
        "type": "AzureDataLakeStore",
        "typeProperties": {
            "dataLakeStoreUri": "https://***.azuredatalakestore.net/webhdfs/v1",
            "servicePrincipalId": "5db67c2***************",
            "servicePrincipalKey": "*******",
            "tenant": "***.onmicrosoft.com",
            "subscriptionId": "42195f*********",
            "resourceGroupName": "RGdev"
        }
    }
}


 Data set:
DummyTab1:

{
    "name": "DummyTab1",
    "properties": {
        "published": false,
        "type": "AzureDataLakeStore",
        "linkedServiceName": "Destination-DataLakeStore",
        "typeProperties": {
            "fileName": "s.txt",
            "folderPath": "output/",
            "format": {
                "type": "TextFormat",
                "rowDelimiter": "\n",
                "columnDelimiter": "\t"
            }
        },
        "availability": {
            "frequency": "Hour",
            "interval": 1
        },
        "external": true,
        "policy": {}
    }
}

 DummyDataSet 2:
{
    "name": "DummyTab2",
    "properties": {
        "published": false,
        "type": "AzureDataLakeStore",
        "linkedServiceName": "Destination-DataLakeStore",
        "typeProperties": {
            "folderPath": "output"
        },
        "availability": {
            "frequency": "Hour",
            "interval": 1
        }
    }
}
 
Pipeline:

{
    "name": "MyADLAParam",
    "properties": {
        "description": "This is a pipeline to compute events for en-gb locale and date less than 2012/02/19.",
        "activities": [
            {
                "type": "DataLakeAnalyticsU-SQL",
                "typeProperties": {
                    "scriptPath": "sprint-release/Sprint6/DimCustomer_split.usql",
                    "scriptLinkedService": "Source-BlobStorageLS",
                    "degreeOfParallelism": 3,
                    "priority": 100,
                    "parameters": {
                        "paramMod": "1"
                    }
                },
                "inputs": [
                    {
                        "name": "DummyTab1"
                    }
                ],
                "outputs": [
                    {
                        "name": "DummyTab2"
                    }
                ],
                "policy": {
                    "timeout": "06:00:00",
                    "concurrency": 1,
                    "executionPriorityOrder": "NewestFirst",
                    "retry": 1
                },
                "scheduler": {
                    "frequency": "Hour",
                    "interval": 1
                },
                "name": "DimCustomerByMod",
                "linkedServiceName": "AzureDataLakeAnalyticsLinkedService"
            }
        ],
        "start": "2017-10-16T22:00:00Z",
        "end": "2017-10-17T12:00:00Z",
        "isPaused": false,
        "hubName": "devadf_hub",
        "pipelineMode": "Scheduled"
    }
}

SSAS Cube Aggregation Design

Several features of your cube design must be borne in mind when designing aggregations, because they can influence how Analysis Services storage engine queries are made and therefore which aggregations will be used. These include:

·         There's no point building aggregations above the granularity you are slicing your partitions by. Aggregations are built on a per-partition basis, so for example if you're partitioning by month there's no value in building an aggregation at the Year granularity since no aggregation can contain more than one month's worth of data. It won't hurt if you do it, it just means that an aggregation at month will be the same size as one at year but useful to more queries. It follows from this that it might be possible to over-partition data and reduce the effectiveness of aggregations, but we have anecdotal evidence from people who have built very large cubes that this is not an issue.

·         For queries involving a dimension with a many-to-many relationship to a measure group, aggregations must not be built using any attributes from the many-to-many dimension, but instead must be built at the granularity of the attributes with a regular relationship to the intermediate measure group. When a query is run using the Sales Reason dimension Analysis Services first works out which Sales Orders relate to each Sales Reason, and then queries the main measure group for these Sales Orders. Therefore, only aggregations at the Sales Order granularity on the main measure group can be used. As a result, in most cases it's not worth building aggregations for queries on many-to-many dimensions since the granularity of these queries is often close to that of the original fact table.

·         Queries involving measures which have semi-additive aggregation types are always resolved at the granularity attribute of the time dimension, so you need to include that attribute in all aggregations.

·         Queries involving measures with measure expressions require aggregations at the common granularity of the two measure groups involved.

·         You should not build aggregations including a parent/child attribute; instead you should use the key attribute in aggregations.

·         No aggregation should include an attribute which has AttributeHierarchyEnabled set to False.

·         No aggregation should include an attribute that is below the granularity attribute of the dimension for the measure group.

·         Any attributes which have a default member that is anything other than the All Member, or which have IsAggregatable set to False, should also be included in all aggregations.

·         Aggregations and indexes are not built for partitions with fewer than 4096 rows. This threshold is set by the IndexBuildThreshold property in msmdsrv.ini; you can change it but it's not a good idea to do so.

·         Aggregations should not include redundant attributes, that is to say attributes from the same 'chain' of attribute relationships. For example if you had a chain of attribute relationships going from month to quarter to year, you should not build an aggregation including month and quarter—it should just include month. This will increase the chance that the aggregation can be used by more queries, as well as reducing the size of the aggregation.

·         Aggregations usability % is higher if the aggregated attribute is at a lower/Medium level in a user defined hierarchy. So it becomes imperative to scan through the attribute relationship usage of Dimension before we add any attribute to aggregation design.

·         To gauze the usage of an aggregation,  Look  out for Query Sub cube , Query sub cube Verbose  and Get data from aggregation events in AS Trace . If these are getting hit , then your MDX query is using the aggregations.

·         Calculated Measure Expressions cause queries to be evaluated at the common granularity of the two measure groups involved. Unary operators other than + and Custom Rollups will also force query granularity down. For Example If Total Impressions =AdExpert Impressions+ AdRevenue Impression . Then Any Mdx query will utilize an aggregation if the designs on the Measure Groups are having aggregation defined at same level of querying Dim attribute else it won’t be utilized.

·         Above All, Aggregations addition/deletion do not UnProcess a cube .But You have to do a process index on selective partition/Measure Group/ whole cube  to see them in effect.

·         Calculated Measure Expressions cause queries to be evaluated at the common granularity. (Aggregation not defined or defined at same grain in appnexus/mocean and adexpert.)

·         Aggregations should not include redundant attributes, that is to say attributes from the same 'chain' of attribute relationships.

 

Temp table(# or ##) versus Table Variable(@)

A Table Variable, like any other variable, the scoping rules of the table variable are similar to any other programming variables. For example, if you define a variable inside a stored procedure, it can’t be accessed outside the stored procedure. A table variable can be very powerful when used with stored procedures to pass it as input/output parameters (new functionality available starting with SQL Server 2008) or to store the result of a table valued function.

·         The table variable is NOT necessarily memory resident. Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb.

·         When you create a table variable, it is like a regular DDL operation and its metadata is stored in system catalog.

·         Table variables don’t participate in transactions or locking.

·         The operations done on table variable are not logged.

·         No DDL is allowed on table variables. So if you have a large rowset which needs to be queried often, you may want to use #table when possible so that you can create appropriate indexes. You can get around this by creating unique constraints when declaring table variable.

·         Finally, no statistics is maintained on table variable which means that any changes in data impacting table variable will not cause recompilation of queries accessing table variable.

 

Difference between table variable and temp table:

·         In functions it is only possible to use a table variable and if you need to write to the table in a child scope then only a #temp table will do (table valued parameters allow readonly access).

·         Indexes: If you need an index that cannot be created implicitly through a UNIQUE or PRIMARY KEY constraint then you need a #temporary table as it is not possible to create these on table variables. (Examples of such indexes are non unique ones, filtered indexes or indexes with INCLUDEd columns). NB: SQL Server 2014 will allow non unique indexes to be declared inline for table variables.

·         If you will be repeatedly adding and deleting large numbers of rows from the table then use a #temporary table. That supports TRUNCATE which is more efficient than DELETE and additionally subsequent inserts following a TRUNCATE can have better performance than those following a DELETE as illustrated here.

·         If the optimal plan using the table will vary dependent on data then use a #temporary table. That supports creation of statistics which allows the plan to be dynamically recompiled according to the data (though for cached temporary tables in stored procedures the recompilation behaviour needs to be understood separately).

·         If the optimal plan for the query using the table is unlikely to ever change then you may consider a table variable to skip the overhead of statistics creation and recompiles (would possibly require hints to fix the plan you want).

·         If the source for the data inserted to the table is from a potentially expensive SELECT statement then consider that using a table variable will block the possibility of this using a parallel plan.

·         If you need the data in the table to survive a rollback of an outer user transaction then use a table variable. A possible use case for this might be logging progress of different steps in a long SQL batch.

·         When using a #temp table within a user transaction locks are held longer than for table variables and also it can prevent truncation of the tempdb transaction log until the user transaction ends. So this might favour the use of table variables.

·         Within stored routines both table variables and temporary tables can be cached. The metadata maintenance for cached table variables is less than that for #temporary tables. Bob Ward points out in his tempdb presentation that this can cause additional contention on system tables under conditions of high concurrency. Additionally when dealing with small quantities of data this can make a measurable difference to performance.

 

References:

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx

http://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

 

SSAS Error: Internal error: Invalid ennumeration value.

I had seen few scenario where you perform any action on Analysis Services 2008 or 2008 R2 you get weird Error Message like:

1. Internal error: Invalid enumeration value. Please call customer support! is not a valid value for this element.
2. An error occurred while parsing the 'StorageMode' element at line 1, column 6751 ('http://schemas.microsoft.com/analysisservices/2003/engine' namespace) under Load/ObjectDefinition/Dimension/StorageMode.
3. Errors in the metadata manager. An error occurred when instantiating a metadata object from the file, '\\?\H:\SSAS\OLAP\Data\CubeName\DimensionName.12.dim.xml'

Reason: Database Files / Stores got corrupted.
The possible causes of Corruption:
- If AS Services was restarted during Processing of a Cube.
- If AS Services was restarted during Backup.
- Sometimes Antivirus locks file and if services restarted during that state it can cause corruption.

Resolution:
Method 1 : If you have permission to create OLAP databases in SSMS then deploy to a new SSAS target database, or delete and recreate the target table.

Method 2: If you want to perform an incremental deployment execute DISCOVER_XML_DATA with ExpandFull option in xmla script editor.
The full command syntax is:

 Then in the BIDS OLAP solution file:

• Right click the dimension reported by the problem and click "View Code".
• Write a character and delete it so that the file seems modified (has a * by its name)
• Save the file and process the dimension alone, without processing the cube
• After this, processing the cude works fine   

Method 3: (Easiest and fastest method)

• Unprocess the dimension which is reported by the error message, in my case - 'Task Created on Date.dim'.
• Then deploy the whole OLAP database from BIDS (visual studio).

Method 4:

• Stop SSAS services.
• Go into the Data directory, find the ..db.xml file and delete it
• Delete the corresponding .db directory.
• Then restart SSAS services.
• The error should be gone, and you can hopefully redeploy and reprocess it.

How to avoid the file corruption in future:
While doing any maintenance task on Server Like Patching / Server Updates or Restart ensure that no user is connected to Analysis Services, this you can ensure by checking profiler traces.

 This problem has been fixed in SSAS 2012.

Reference:

• http://blogs.msdn.com/b/karang/archive/2010/08/11/how-to-deal-with-corruption-in-analysis-services.aspx
• http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/375e3446-ef18-4376-80bc-00f2e742775b
• http://blogs.msdn.com/b/as_emea/archive/2012/12/05/ssas-project-deployment-fails-with-error-quot-internal-error-invalid-enumeration-value-please-call-customer-support-quot.aspx

Search for a column in database.

Search for the column in tables or views:
SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE column_name LIKE '%Total Amount%'

ORDER BY 3,4

 Search for the database object in all the database objects:
SELECT OBJECT_NAME(OBJECT_ID),
definition

FROM sys.sql_modules

WHERE definition LIKE '%Total Amount%'

 Search for the column only in procedures:
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),

object_definition(OBJECT_ID)

FROM sys.Procedures

WHERE object_definition(OBJECT_ID) LIKE '%Total Amount%'

MDX: Last Available value

As the situation explained in my previous post, last-non-empty-value-in-dax we might have to give the last available value of say balance amount in my bank account. So for that we need to travel back in time axis to get the last non-empty value for a given measure at any point in time.

There are couple of methods you can follow depending on the scenario:
Method 1: This is the simple recursive query which gets the last available data from the cube.

WITH

 MEMBER [Measures].[ProdAmount] AS
  IIF(
    ISEMPTY([Measures].[Amount]),

      ([FiscalPeriod].[Calendar].PREVMEMBER,[Measures].[ProdAmount])

      ,[Measures].[Amount])

SELECT [Measures].[ProdAmount] on 0,

[FiscalPeriod].[Calendar].[Fiscal Month] on 1
FROM (SELECT [Product].[Product Name].&[Bike]     ON 0
      FROM [Project])

 One point to remember is the cube should atleast have data for one dimension combination that we are going to query on. If we need the last available Product amount, then for every product in the product dimension we need Amount for atleast one day in time dimension . Else the Measure.ProdAmount goes into infinite loop.

Method 2. You can override the above problem using Nonempty and Tail function: The following query gives the latest product amount given for a particular product for the given year. Here the calculated measure, ProdAmount firstly calculates all the nonempty month, measure set. And then takes the latest month, amount set for the given Product.

If there is no data for a given product for any date in time dimension then the Measure.ProdAmountLA returns value as NULL.

MEMBER [Measures].[ProdAmountLA] AS

TAIL(
  NONEMPTY(
    [FiscalPeriod].[FiscalMonth].CHILDREN*[Measures].[Amount]),1).ITEM(0)


Method 3: There are scenarios were the NULL values in cube is automatically replaced as 0 by default. This happens when the NULLProcessing property of the measure is set as 'Automatic' instead of 'Preserve'. http://msdn.microsoft.com/en-us/library/ms345138(SQL.90).aspx

In this case we cannot use Nonempty function as it will return null values as they are replaced as 0 in the cube. So we will filter out data which are not 0
MEMBER [Measures].[ProdAmountLA] AS
   TAIL(
     Filter(
         [FiscalPeriod].[FiscalMonth].CHILDREN *[Measures].[Amount]

         ,[Measures].[Amount] <> 0),1).ITEM(0)

More Details: http://cwebbbi.wordpress.com/2011/03/24/last-ever-non-empty-a-new-fast-mdx-approach/

flaging duplicate without using any temp table and row_id

with TBLCTE(slno,C1,C2,C3,c4,Ranking)
AS
(
select
slno,C1,c2,c3,c4,
Ranking = DENSE_RANK() over (PARTITION BY slno,C1,C2,C3 order by slno,newID())
from trans where C4 <> 'Y'
)
update TBLCTE set C4 = 'Y' where Ranking > 1

select * from trans order by Slno,c1,C2,C3

Excel validate filename

  public void Main()
        {
            // TODO: Add your code here
            string strFileName = string.Empty;
            bool bFileSuccess;
            string strUniqueFileName = string.Empty;

            strFileName = GetSourceFileName(out bFileSuccess);
            if (!strFileName.Equals(string.Empty))
            {
                if (bFileSuccess)
                {
                    LogFileName(strFileName, bFileSuccess);
                    Dts.Variables["pExtractDate"].Value = dtFileExtract;//Set File extract date
                }
                else
                {
                    Dts.TaskResult = (int)ScriptResults.Failure;
                    LogFileName(strFileName,bFileSuccess, "Wrong source file format");
                }
            }
            else
            {
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            Dts.Variables["pVarFileName"].Value = strFileName;

            ////strUniqueFileName = System.DateTime.Now.ToString("dd-MM-yyyy") + System.DateTime.Now.TimeOfDay.Milliseconds.ToString() + ".xls";
     
            //////Create a copy of file
            ////FileInfo fiSourceFile = new FileInfo(strSourceFile);
            ////fiSourceFile.CopyTo(Dts.Variables["InputFileFolder"].Value.ToString() + strUniqueFileName, true);
         
            ////Dts.Variables["TabFileSource"].Value = Dts.Variables["InputFileFolder"].Value.ToString() + strUniqueFileName;
        }
        public String GetSourceFileName(out bool bFileSuccess)
        {
            String strRetSourceFile = string.Empty;
            Boolean blDisableTaskFlag = false;
            String strSourceFolder = Dts.Variables["InputFileFolder"].Value.ToString();
            String strSourceFileTemplate = Dts.Variables["FileNameTemplate"].Value.ToString().ToLower();
            DirectoryInfo diSourceFolder = new DirectoryInfo(strSourceFolder);
            FileInfo[] fiSourceFiles = diSourceFolder.GetFiles();
            bFileSuccess = true;
            foreach (FileInfo fiSourceFile in fiSourceFiles)
            {
                if (fiSourceFile.Name.ToLower().Contains(strSourceFileTemplate))
                {
                    strRetSourceFile = fiSourceFile.Name;
                    String strDatePart = strRetSourceFile.Substring(strSourceFileTemplate.Length);
                    strDatePart = strDatePart.Substring(0, strDatePart.Length - 4);
                    Dts.Variables["FileSource"].Value = fiSourceFile.FullName;
                 
                    //Form File extraction date

                    if (!DateTime.TryParse(strDatePart, out dtFileExtract))
                    {
                        bFileSuccess = false;
                    }
                    blDisableTaskFlag = false;
                    break;
                }
                else
                {
                    blDisableTaskFlag = true;
                }
            }
         
            if (strRetSourceFile.Equals(string.Empty))
            {
                blDisableTaskFlag = true;
                //blDisableTaskFlag = false;
            }
            Dts.Variables["DisableTask"].Value = blDisableTaskFlag;
            return strRetSourceFile;
        }
        public void LogFileName(string strFileName,bool bSuccess)
        {
            LogFileNameEx(strFileName,bSuccess, "");
         
        }
        public void LogFileName(string strFileName,bool bSuccess,string strMessage)
        {
            LogFileNameEx(strFileName,bSuccess, strMessage);
        }
        private void LogFileNameEx(string strFileName, bool bSuccess,string strMessage)
        {
            OleDbConnection sqlConnection = new OleDbConnection(Dts.Connections["ITGWarehouse"].ConnectionString);
            using (OleDbCommand cmdUspFileProcessingDetailsOnBegin = new OleDbCommand("MD.usp_GetFileProcessingList"))
            {
                cmdUspFileProcessingDetailsOnBegin.Connection = sqlConnection;
                cmdUspFileProcessingDetailsOnBegin.CommandType = CommandType.StoredProcedure;
                cmdUspFileProcessingDetailsOnBegin.Parameters.Add("@pExecutionLogId", SqlDbType.Int).Value = Dts.Variables["LogID"].Value;
                cmdUspFileProcessingDetailsOnBegin.Parameters.Add("@pFileType", SqlDbType.VarChar).Value = Dts.Variables["FileNameTemplate"].Value;
                cmdUspFileProcessingDetailsOnBegin.Parameters.Add("@pFileName", SqlDbType.VarChar).Value = strFileName;
                cmdUspFileProcessingDetailsOnBegin.Parameters.Add("@pMessage", SqlDbType.VarChar).Value = strMessage;
                if (bSuccess)
                    cmdUspFileProcessingDetailsOnBegin.Parameters.Add("@pExtractDate", SqlDbType.DateTime).Value = dtFileExtract;
                else
                    cmdUspFileProcessingDetailsOnBegin.Parameters.Add("@pExtractDate", SqlDbType.DateTime).Value = DBNull.Value;
                try
                {
                    sqlConnection.Open();
                    cmdUspFileProcessingDetailsOnBegin.ExecuteNonQuery();
                }
                finally
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
            }

        }

Excel retrieve sheet by sheet

Excel connection:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Source\Product\20081202_Product.xls;Extended Properties="EXCEL 8.0;HDR=NO";

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+@[User::filenameProduct]+";Extended Properties=\"Excel 8.0;HDR=NO\";"

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\assignment related\Source\Product\20081202_Product.xls;Extended Properties="Excel 8.0;HDR=YES";

  public void Main()
        {
            ////Microsoft.SqlServer.Dts.Runtime.ConnectionManager cm = Dts.Connections["HS_Incidents"];
            ////IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as IDTSConnectionManagerDatabaseParameters100;
            ////OleDbConnection conn = cmParams.GetConnectionForSchema() as OleDbConnection;
            ////conn.Close();
         
            string excelFile;
            string connectionString;
            OleDbConnection excelConnection;
            DataTable tablesInFile;
            int tableCount = 0;
            string currentTable;
            int tableIndex = 0;

            excelFile = Dts.Variables["FileSource"].Value.ToString();
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + excelFile + ";Extended Properties=Excel 8.0";
            excelConnection = new OleDbConnection(connectionString);
            excelConnection.Open();
            tablesInFile = excelConnection.GetSchema("Tables");
            tableCount = tablesInFile.Rows.Count;

            string[] excelTables = new string[tableCount];
         
            foreach (DataRow tableInFile in tablesInFile.Rows)
            {
                currentTable = tableInFile["TABLE_NAME"].ToString();
                //GetRowsOfCurrentTable(currentTable, excelConnection.GetOleDbSchemaTable(
                excelTables[tableIndex] = "Select [" + currentTable + "].* From " + "["+ currentTable + "]";
                tableIndex += 1;
            }
            excelConnection.Close();
            //System.IO.FileInfo x = new System.IO.FileInfo(Dts.Variables["FileSource"].Value.ToString() );
            //x.Delete();
            //System.IO.File.Move(excelFile, Dts.Variables["ValidSourceFile"].Value.ToString());
            Dts.Variables["ExcelTables"].Value = excelTables;

            Dts.TaskResult = (int)ScriptResults.Success;

        }
    }

Power BI: DAX: Using Multi Select Slicer show different charts for each selected item.

1. AllSelection = IF(NOT(ISFILTERED(PLDeviceID[VideoSportClean])),"All",
CONCATENATEX(ALLSELECTED(PLDeviceID[VideoSportClean]),PLDeviceID[VideoSportClean],","))&","

2. AllSelectionCount = LEN([AllSelection])-LEN(SUBSTITUTE([AllSelection],",",""))

3. FirstMeasure = CALCULATE(SUM(PLDeviceID[LiveStreamStartsEvent19]),FILTER(PLDeviceID,PLDeviceID[LiveStreamStartsEvent19]>0),FILTER(ALLSELECTED(PLDeviceID),PLDeviceID[VideoSportClean] =[FirstSelection]))

4. FirstSelection = LEFT([AllSelection],SEARCH(",",[AllSelection])-1)

5. SecondMeasure = CALCULATE(SUM(PLDeviceID[LiveStreamStartsEvent19]),FILTER(PLDeviceID,PLDeviceID[LiveStreamStartsEvent19]>0),FILTER(ALLSELECTED(PLDeviceID),PLDeviceID[VideoSportClean] =[SecondSelection]))

6. SecondSelection = MID([AllSelection],SEARCH(",",[AllSelection])+1,SEARCH(",",[AllSelection],SEARCH(",",[AllSelection])))

Running Total 
Last Available Value

DAX query examples: DAX Running Total:

DAX Running Total:

git clone git@gist.github.com:92e8e3b7a1a212549af4.git gist-92e8e3b7

How to find if data has been loaded into database

Here is a simple way to find if the data has been loaded into database without checking every table one after another. This returns a single statement which has count of all the records in all the tables. You can also provide the table schema to narrow your query process.


DECLARE @sql VARCHAR(max), @tableSchema VARCHAR(5)
SET @sql = ''
SET @tableSchema = 'DW'
SELECT @sql = @sql + 'SELECT COUNT(*) as Count,'''+TABLE_NAME+''' AS table_Name FROM '+TABLE_SCHEMA+'.' +TABLE_NAME +' UNION '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @tableSchema
SET @sql = LEFT(@sql,LEN(@sql)-LEN('union'))
SET @sql = @sql+' order by Count'
EXEC (@sql)

DAX References

Read more about the types of DAX functions: 

• Date and Time Functions 
• Filter Functions
• Information Functions
• Logical Functions
• Mathematical and Trigonometric Functions
• Statistical Functions
• Text Functions
• Time Intelligence Functions
•  Some Additional links

DAX Operator reference:

• Operator Reference
• Appendix DAX Reference

For more resource check:

• DAX Resource Center.
• How filters behave.

For those who want to relate DAX with MDX and SQL, kindly refer the following link:

• Converting MDX to DAX

Adventure Works database:
http://www.ssas-info.com/analysis-services-faq/29-mgmt/242-how-install-adventure-works-dw-database-analysis-services-2005-sample-database

Last Non Empty Value in DAX

While creating a Dashboard where the data comes from both OLTP(transaction data) and OLAP (analysis data), the common issue we face is OLAP chart shows "No Data Available" error while the OLTP chart gives the data. So we need to find a solution where we can find the last available value for the OLAP data, so that the dashboard becomes meaningful. This issue has been illustrated in the following example.
Scenario:
I have two tables-
1.FactAssetAllocation: Its a OLTP table. This table records all the asset allocation transaction details. The data is captured every day (or every month depending on your system).
2. FactCommitments. Its a OLAP table. This table records the commitment amount details for each of the client. The data is captured when ever there is a new commitment is done with the client.

Now i had to create a dashboard which has two charts:
1. Is an asset allocation bar-chart.
2. Is a table which show the commitment details for a given client.
The dashboard filter is Date and Client.

Problem:
When i select a Client (example Alan) and a Date (example 2011.11.09) You find there is Asset Allocation data for the given client and date but there is no commitment details.

This is because the Asset Allocation has data for Client - Alan, Date - 2011.01.09, but there is no data for Client -Alan on the same date. the commitment for Alan was done on 2011-04-12. So this data has to be shown in the commitment status chart.
So now we need to get Last Non Empty (Last Available) Commitment value for the given Asset_Allocation[Date_key].
If i had to write a SQL query it will be:

SELECT Commitment_Amount 
FROM DW.Fact_Commitment  
                      INNER JOIN DW.Fact_AssetAllocation A ON A.Client_Key = C.Client_Key
WHERE Date_key in 
        (
          SELECT MAX(C.Date_Key)
          FROM DW.Fact_Commitment C 
                      INNER JOIN DW.Fact_AssetAllocation A ON A.Client_Key = C.Client_Key
        )

If the same query needs to be written in DAX:

CALCULATE(
MAX(Commitments[Commitment])
,FILTER(
all(Commitments)
,Commitments[Client_Key] = MAX(AUM[Client_Key])
)
,FILTER(
ALL(Commitments)
,Commitments[Date_Key] =CALCULATE(
MAX(Commitments[Date_Key])
,FILTER(
ALL(Commitments)
,Commitments[Date_Key] <= MAX(AUM[Date_Key])
)
,FILTER(
all(Commitments)
,Commitments[Client_Key] = MAX(AUM[Client_Key])
)
)
)
)

The Blue section highlighted gives the Last Available Commitment Date for which we need to find the commitment value.
More Links: http://sqlblog.com/blogs/alberto_ferrari/archive/2011/03/08/thinking-in-dax-counting-products-in-the-current-status-with-powerpivot.aspx

How to preserve the font style properties in SSMS

I had a requirement where i was asked to store the input data to be stored in the user font style format that we receive. we were asked to store data with font style like bold, italics, fonts with indicators like red or green, sentences with new line etc... to be stored as is. The challenge was t-sql always stores all the data in the standard format and there wasn't any way to store it with the styles. 

So one method which i have found is to store the data in the database with html tags: like '<b><font color="red">Worse</b></font>' as Column_A. 

You can store all the details from the client as html tags with in a table in the database for the later use. And when required we can pull the data through an external application.

For instance write the following query in your SSMS: 

SELECT ''<htmL><b><font color="green">Better</b></font></html>'' AS [Status]

Choose Result to File option (as shown in the screenshot)  in the tool pane.

Execute the query. 

A pop up window appears, specify the location where you want to store the file.

Store the result into a  .doc file.

The word "Better" is saved as bold green in color.

Note: If you store the file as .txt file, the result is saved as it is with the html tags.