Cosmos DB Advantages

Cosmos DB is gaining Traction exponentially & I would like to list the advantages of Cosmos DB here.

Globally Distributed means scalable across all countries & regions.

Low Latency is the core advantage of Cosmos DB.  This is achieved through SSD based storage, planet scale flexibility providing Millisecond response time.

Flexible Consistency offered through Varying Consistency Models like Strong, Boundless, Session, Prefix, Eventual.

Flexible Pricing Model allows metering of storage & throughput independently rather than CPU/Memory/IOPs.

Elastic Scalability will scale the database based on the request volumes with No Limits. aka Horizontal Scalability

Multi-Storage Model allows storage of data in form of key-value pairs, documents, graphs & columnar

Multi-API Model allows querying the database in SQL for document, MongoDB for document, Azure Table Storage for key-value, Cassandra for columnar etc.

High Security  achieved through End-to-end Encryption on data-in-transit & data-at-rest.

Application Insights in Azure

Application Insights is a Application Performance Management (APM) Service available for Developers & Administrators.  We can use it for web applications deployed to Azure.

Application Insights provides the following features:

  • Application Availability
  • Performance Monitoring
  • Usage Insights

Application Insights also provides Visual Studio Integration, Support for Windows Phone Store Apps etc.

Creation of Application Insights

Open Azure Portal > Create New Resource > Search Box > Enter Application Insights

image

Select the first option & Choose Create.

image

Enter Information in the appearing dialog.

image

Click the Create button in the bottom.

Wait a few minutes & your Application Insights will be created.

image

Now you are ready with the Application Insights panel as shown below.

image

Cosmos DB

In this article we can explore about Cosmos Db database & its usage.

Cosmos DB

Azure Cosmos DB is a globally distributed, multi-model, planet-scale, low-latency, high-availability, No-SQL database stored as JSON format.

image

In the olden days Cosmos DB was called as Azure DocumentDB.

Planet Scale

Azure Cosmos DB will be replicated based on Geography region & hence low-latency. There will be Primary Write-database in one central location & Multiple Secondary Read-databases in multiple locations.

Due to the multiple geographical location availability low-latency & high-availability is possible.

Multi-API Support

Cosmos DB supports Multiple-API support:

· SQL API

· Mongo API

· Graph API

· Table API

· Cassandra API

JSON Format

JSON Format is used to store the data in Cosmos DB.

Consistency

Cosmos DB have 5 consistency levels to ensure consistency, availability and latency trade-offs.

Create Cosmos DB

We can create Cosmos DB from Azure Portal as shown below.

Open Azure Portal > Create new resource > Cosmos DB

image

image

Click the Ok button & Wait for few minutes for the Creation to be completed.

You will get the following screen after creation.

image

Start by Add Collection. Then Add a New Document.

image

You can add new properties in the document. The data is stored as JSON file. Ensure the ID is unique.

image

Now we can query the database using SQL. Choose the New SQL Query option > Enter SELECT statement & Execute the Query. You will get the following results as displayed.

image

Summary

In this article we have explored about Cosmos Db database & its usage.

My DAX Days…

In this post I would like to Record my DAX Learnings during my SQL Server Analysis Services days.

SQL Server Analysis Services

It is 1000X faster than SQL Server since the Data is stored in Memory in Compressed Format.

DAX

DAX stands for Data Analysis Expressions.  It is widely used in Analysis Services, Power BI etc. 

DAX looks similar to Excel Formulas.

DAX Measure

DAX Measure is the actual Formula.

DAX Variables

DAX Variables will be executed once they are Invoked

EVALUATE

Evaluates the Expression.

Eg: EVALUATE ( ‘DimProducts’)

Eg: EVALUATE( ‘DimProducts’ ) ORDER BY ‘DimProducts'[Date] DESC  –Sorting Example

For Scalar Values, Use EVALUATE (ROW(“Column”, Variable)) syntax

Note: Add an EVALUATE in front of the Expression

ROW

Returns a Table with a Single Row.

Eg: EVALUATE ROW(“Total Sales”, SUM(FactSales[Price]), 
         “Total Quantity”, SUM(FactSales[Quantity]))

image

SUMMARY

Returns a summary table.

EVALUATE
SUMMARIZE(
        DimProducts 
      , DimProducts[Id] 
      , DimProducts[Class] 
      , DimProducts[SubClass] 
      , “Sales Total”, SUM(FactSales[Price]) 
      , “Quantity Total”,  SUM(FactSales[Quantity]) 
      ) 

image

CALCULATE

Evaluates an Expression when modified by a Filter.

FILTER

Returns a Subset of Table or Expression.

Eg: EVALUATE
FILTER(DimProducts,DimProducts[Class]=”Home Theatre”)

image

*Note the double quotes

More Filters: https://bit.ly/2L3iOdr

SUM

Sum is an Aggregator.

Eg: EVALUATE
SUMMARIZE (
DimProducts,
“Sum”, SUM ( DimProducts[Price] )
)

image

SUMX

SumX is an Iterator.  Can Add Multiplications into this.

Eg: EVALUATE
SUMMARIZE (
DimProducts,
“Sum”, SUMX (DimProducts, DimProducts[Price] * 100)
)

image

ADDCOLUMNS

Adds calculated columns to the given table

References

https://www.sqlbi.com/

https://msdn.microsoft.com/en-us/query-bi/dax

More DAX Days

In this post I would  like to Publish few more DAX scenarios I have encountered.

Join Tables

We can use CROSSJOIN function on tables to use All Columns required by the SUMMARIZE function.

DEFINE
    VAR JoinTable =
        FILTER (
            CROSSJOIN ( Products, FactSales ),
            DimProducts[Id] = SalesFactSales[ProductId]
        )

    VAR Result Table=
        SUMMARIZE (
            JoinTable ,
            DimProducts[Choice],
            DimProducts[Id],
            FactSales[StoreID],
            FactSales[WeekId],
            “Quantity”, SUMX (
                FILTER ( ‘FactSales’, ‘DimProducts'[Id] = ‘FactSales'[ProductId] ),
                FactSales[Quantity]
            )
        )

 

EVALUATE
ResultTable

SUMMARIZECOLUMNS

SUMMARIZECOLUMNS is recommended than SUMMARIZE because it is Faster & Optimized.

SUMMARIZE still exists for Backwordk Compatibility.

Let us see the Cross Join using SUMMARIZECOLUMNS.

Cross Join with Matching ProductID

DEFINE VAR Results =
SUMMARIZECOLUMNS (
    ‘DimProducts'[ProductID],
    “Sales Amount”,
    SUMX (        FILTER (
            CROSSJOIN ( DimProducts, FactSales ),
            DimProducts[ProductId] = FactSales[ProductId]
        ), FactSales[Quantity] * FactSales[Price] )

)

EVALUATE Results

image

Another way to see the results.

EVALUATE FILTER(CROSSJOIN (
                        DimProducts,
                        FactSales
                    ),
                    DimProducts[ProductId] = FactSales[ProductId]

)

— Returns as many rows as in Sales table

image

Working on Table Variables

We can work on Table Variables to do more Aggregations. Example is given below.

For example to find Average, Use AVERAGEX function.

VAR ResultsTable =
        ADDCOLUMNS (
            SUMMARIZE (
                ProductJoinSales,
                DimProducts[Choice],
                DimProducts[Id]
            ),
            “CalcValue”,  DIVIDE(
                DIVIDE (
                    SUM (
                        FactSales[Quantity]
                        ),
                        COUNT ( FactSales[StoreID] )
                )
            )
        )

VAR Result = AVERAGEX (ResultsTable, [CalcValue]) 

Note Ensure to display the value using ROW function.

EVALUATE ROW(“Average”, Result)

OR Condition in Filter

Or Conditions in Filters are pretty easy.  Use the || operator of C#.

FILTER ( FactSales, FactSales[Price] > 0 || FactSales[Quantity] > 0)

Reference

https://azure-arena.com/2018/08/03/my-dax-days/

https://azure-arena.com/2018/08/01/sql-tables-used-in-dax/

Azure VM–Save Cost by Auto-Shutdown

Azure VM definitely gives lot of flexibility like:

  • High-end Hardware Configuration
  • Ready Software
  • High-speed Internet
  • Quick Availability

At the same time the Cost can be Quite Huge if one is considering High Configuration with Costly Software Licenses.

Save Cost

Most of the time I have noticed the Users will be using VM only less than 6 hour per day.  Rest of the time it will be left alone.  This is simply eating lot of $ per hour if you calculate the hours X days for a Month.

Auto-Shutdown Feature

Here the Auto-Shutdown feature comes handy. Here are the steps to use it:

Open VM > Control Panel

image

In the appearing blade choose the Enabled option.

image

You can also set an Email for notifying you before the shutdown.