Quality & Perfection wins the Long-Run!
This is a filler post to be used elsewhere.
Open your Azure Portal. Click on Azure Active Directory.
Click on the Properties from the left pane.
In the appearing page you will see the Directory ID property. That is the Tenant ID!
Singleton Design Pattern ensures that Only One Instance is created. But, It will impose challenges in the Multi-threaded scenarios.
public class ConnectionPool
{
private static ConnectionPool instance;
private static object _lockObject = new object();
public static ConnectionPool GetInstance()
{
if (instance == null)
{
lock (_lockObject)
{
if (instance != null)
instance = new ConnectionPool();
}
}
return instance;
}
private ConnectionPool()
{
// doesn’t matter what’s here
}
}
Reference
I have written a book Design Patterns in C# few years back.
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.
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
Click the Ok button & Wait for few minutes for the Creation to be completed.
You will get the following screen after creation.
Start by Add Collection. Then Add a New Document.
You can add new properties in the document. The data is stored as JSON file. Ensure the ID is unique.
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.
Summary
In this article we have explored about Cosmos Db database & its usage.
If you have not Selected Data Processing templates during your Visual Studio Installation, then you need to Download the SSDT for VS 2017 from following URL:
https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017
Installation
On running the downloaded executable file, you will get the following prompt. Choose the First Option if you want to work with All Templates in current Visual Studio instance. Also, Select the Analysis Services option.
Click the Next button to start Installation.
Once the Installation is completed, You will get the following message.
Inside Visual Studio
Open Visual Studio. If you can see following Templates then you are good!
Summary
This post is a Part of Starting with Azure Analysis Services series. Please check other Articles for the continuity.
In this post I would like to Record my DAX Learnings during my SQL Server Analysis Services days.
It is 1000X faster than SQL Server since the Data is stored in Memory in Compressed Format.
DAX stands for Data Analysis Expressions. It is widely used in Analysis Services, Power BI etc.
DAX looks similar to Excel Formulas.
DAX Measure is the actual Formula.
DAX Variables will be executed once they are Invoked
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
Returns a Table with a Single Row.
Eg: EVALUATE ROW(“Total Sales”, SUM(FactSales[Price]),
“Total Quantity”, SUM(FactSales[Quantity]))
Returns a summary table.
EVALUATE
SUMMARIZE(
DimProducts
, DimProducts[Id]
, DimProducts[Class]
, DimProducts[SubClass]
, “Sales Total”, SUM(FactSales[Price])
, “Quantity Total”, SUM(FactSales[Quantity])
)
Evaluates an Expression when modified by a Filter.
Returns a Subset of Table or Expression.
Eg: EVALUATE
FILTER(DimProducts,DimProducts[Class]=”Home Theatre”)
*Note the double quotes
More Filters: https://bit.ly/2L3iOdr
Sum is an Aggregator.
Eg: EVALUATE
SUMMARIZE (
DimProducts,
“Sum”, SUM ( DimProducts[Price] )
)
SumX is an Iterator. Can Add Multiplications into this.
Eg: EVALUATE
SUMMARIZE (
DimProducts,
“Sum”, SUMX (DimProducts, DimProducts[Price] * 100)
)
Adds calculated columns to the given table
In this post I would like to Publish few more DAX scenarios I have encountered.
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 is recommended than SUMMARIZE because it is Faster & Optimized.
SUMMARIZE still exists for Backwordk Compatibility.
Let us see the Cross Join using SUMMARIZECOLUMNS.
DEFINE VAR Results =
SUMMARIZECOLUMNS (
‘DimProducts'[ProductID],
“Sales Amount”,
SUMX ( FILTER (
CROSSJOIN ( DimProducts, FactSales ),
DimProducts[ProductId] = FactSales[ProductId]
), FactSales[Quantity] * FactSales[Price] )
)
EVALUATE Results
Another way to see the results.
EVALUATE FILTER(CROSSJOIN (
DimProducts,
FactSales
),
DimProducts[ProductId] = FactSales[ProductId]
)
— Returns as many rows as in Sales table
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 Conditions in Filters are pretty easy. Use the || operator of C#.
FILTER ( FactSales, FactSales[Price] > 0 || FactSales[Quantity] > 0)
Reference
Following are the SQL Tables Schema used in the DAX exercises.
CREATE TABLE [dbo].[DimProducts](
[ProductID] [varchar](50) NOT NULL,
[Name] [varchar](50) NULL,
[Class] [varchar](50) NULL,
[SubClass] [varchar](50) NULL,
[Price] [float] NULL,
[Choice] [varchar](50) NULL,
CONSTRAINT [PK_DimProducts] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[FactSales](
[SalesId] [varchar](50) NOT NULL,
[Date] [date] NULL,
[Quantity] [int] NULL,
[Price] [float] NULL,
[ProductID] [varchar](50) NULL,
[WeekID] [int] NULL,
[StoreID] [int] NULL,
CONSTRAINT [PK_FactSales] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
The one-to-many relation is shown as below.
