BI Power: Denali

Showing posts with label Denali. Show all posts

Wednesday, September 5, 2012

DAX query examples: DAX Running Total:

DAX Running Total:

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

Monday, July 30, 2012

DAX References

Read more about the types of DAX functions:

DAX Operator reference:

For more resource check:

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

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

Wednesday, April 11, 2012

BI Semantic Tabular Model Reporting

SQL Server 2012 has provided customers with large-scale data warehousing and analytical solutions backed by IT management and insights. New and enhanced self-service capabilities allow users to explore and unlock new insights from information spread across structured and unstructured data sources, both on-premises and in the cloud.

Why BI Semantic Model (BISM)

The BI Semantic Model is a new hybrid data model that offers the powerful analytical capabilities of multidimensional data models as well as the simplicity and familiarity of tabular (that is, relational) model.

Why BISM Tabular Model

As part of the BI Semantic Model, SQL Server 2012 Analysis Services introduces a new tabular modeling capability that allows users to build a BI model using exactly the same processes that were introduced in PowerPivot for Excel. Users can quickly bring together data in tables and build relationships using the DAX query language to apply business logic.

The following section explains simple steps involved in developing a BISM tabular model.

Steps to develop your first BISM Tabular model

The following are the steps involved to develop your first BISM Tabular Model.

1. Install an Analysis Services Instance Running in Tabular Mode:

Either you can add new features to the existing instance or create a new instance for Tabular Model.

a a) To add new instance kindly follow the steps:

b) Select the Server Mode: Tabular Mode and add yourself as the administrator.

c) Verify if the tabular model is installed by connecting to the analysis server instance name that you have just created.

2. Kick Start:

The following steps needs to be followed to create a new BISM project:

a) Creating a BISM project: Open BIDS(Business Intelligence Development Studio) and click on new project. There are three types of project template:

i) Analysis Service Tabular Project: Use this template to create an Analysis Service project with tabular models.

ii) Import from PowerPivot: Use this template to create a tabular project by extracting the metadata and data from an existing PowerPivot workbook.

iii) Import from Server (Tabular): Use this template to create a tabular project by extracting the metadata from an existing tabular AS server

For our development choose Analysis Service Tabular Project.

b) Importing data: Double click the model.bim. This will open a container to browse all the tables in the model. At present its empty as we have not imported any data from database.
To import data select Model from Menu bar and click on Import from Data Source.

A Table import Wizard pops up with different data sources to which the model can be connected.
Select Microsoft SQL Server and click next.
Provide the required details to connect to the relational database.
Next, choose the dimension and fact tables and click finish.
Now you have successfully imported data into your model.

3. Creating Relationships:

You can create relationships between tables by switching to the diagram view (You can switch from grid view to diagram view by selecting the icon at the bottom right corner). If the tables already have foreign key relationships established they will be retained, otherwise you can create relationships between the tables manually.

4. Creating Measures:

Measures are created in the grid view. Switch to grid view and select the fact table. Choose the column for which u want to create the measure and select Sum from the Toolbar. This will create a measure for Sum of fact value, here sum of SalesAmount in my case. Any measure can be created with any type of calculation.

5. Make your Model User Friendly:

Hide the columns which is not meaningful for the user.
For example: Surrogate keys, foreign_Keys, audit columns, etc.

You can do this either by setting the hidden to true in column property window else right click the column and select "Hide from Client Tools"

Give user friendly names to the columns and tables.

6. Creating Hierarchy:

Switch to diagram view. Select the columns that needs to be added to the hierarchy, right click and select Create Hierarchy if the hierarchy is been newly created else choose Add to Hierarchy and choose the hierarchy name.

7. Create Calculated Column and Measures:

We make use of DAX (Data Analysis Expression) to create calculated columns and measures. There are more than 30 new functions added like distinct, count, sum, rank, lookupValue etc.
To create a calculated column in the grid view add another column with the calculation. Its similar to named calculation in OLAP database and evaluated at the row context.

For example: [User Full Name] := [User First Name] + " " + [User Last Name]

To create a calculated measure, add your calculation at the measure grid. The values are evaluated at the filter context. These are placed at the values area of Power Pivot tables.

For example: [Average Sale] = AVERAGEX ( Sales, Sales[Amount])

8. Changing the Source Query and Adding New Tables:

We can also have the flexibility to change the source query of each table. To attain this switch to Data View and select the table. In the property window select Source Data (...) and switch to query editor and write your query here.

You can also add related dimension and fact tables at the later point of time to the model by choosing either "import from Data Base" or choose "existing connection" in the toolbar.

9. Deploying Model:

Ensure the deployment options are set correctly. To check right click the solution and select properties. Edit the deployment options and click ok. Now your model ready and you can deploy by choosing Deploy in the solution right click options.

10. Reporting:

You can also analyse the data in excel by selecting Excel symbol (Analyse in Excel) in the Toolbar. A pivot table is created by connecting to the semantic model just created. You can create excel dashboard reports, perform analysis of data easily.

Conclusion

The user experience of creating tabular data models in PowerPivot is similar to the user experience of creating an SSAS tabular project in SQL Server Data Tools. BISM Tabular models are similar to Excel and include easy-to-use formulas which make them useful for creating solutions with high agility and time to availability. For more informantion and additional resources follow the link below.

BI Power