Showing posts from August, 2012

Agile dashboard creation with Tableau 7.0

Here I would like to explain the capabilities of Tableau 7.0 for agile dashboard creation. I got an opportunity to work for a POC using tableau 7.0 which is is widely used for analytical dashboard creation. Same POC were developed using other rapid dashboard development tools such as Spot fire, Xcelsius and Microstrategy. Tableau didn’t come to number 1 position for this POC as the requirement basically created (wireframe) based on the capabilities of the Xcelsius and we know that every tool has its own advantage and disadvantage. Being a MSBI professional, I am so amazed by the capabilities of MS-Excel which helped me highly during my AdCenter analytics project. MS- Excel is having various graphs, charts, Sparkline etc. to show the data visually and Pivot and slicing option increases its analytical ability. MS-Excel has more features compared to Tableau 7.0, but Tableau 7.0 stand top on visualization and easiness in creation of dashboards. Tableau can connect various database

MS-SQL : Stored procedure coding standard/check list

It is always better to have check list for creating stored procedure in SQL server. Most people may not think that it is really required to have; yes I agreed that for experienced developer it may not be required because by default they use the entire coding standard because of their experience. But during my 8 years of experience I have seen many of experienced developers also forgetting basic principles and hence this post. 1. Procedure name should be as per the standard provided by the client or decided by the project team. 2. Proper comments to understand the functionality. 3. Name of the preson created, date,  purpose,sample execution statement at the beginning as shown in fig 1. 4. All the syntax should be in CAPS 5. Align the code with right indentation- this helps to understand the code very easily 6. Proper exception handling as shown in Fig 2 7. Raise error appropriately to help the user understand what exactly happened. 8. Keep declaration, initiation etc. in sepa

SSAS - Message group has zero dimensional overlap

It is always better to have seperate cube for each measure group if there are no common dimension between them. But if you have added multiple measure group in same cube and not added the common dimension in the dimension usage, SSAS gives warning message " The measure group has zero dimensional overlap with all of the other measure groups in the cube. Consider moving it to the seperate cube " To demonstrate this, I have created cube with internetsales and internetSalesReason from AdventureworksDW database. Initially there are no common dimension and so I have received the error as showned in Fig 0.1.  Fig 0.1 There are no common dimension between the measure group InternetSales and InternetSalesReason as shown in Fig 0.2 Fig 0.2 In order to avoid this issue, we can have common dimension between these two tables by adding SalesOrderNumber as cube dimension. SalesOrderNumber is actually saved in InternetSales table itself which is a fac

Excel : Filter pivot data dynamically using macro

In this post I would like to explain very easy method to filter pivot data dynamically using macro. To demonstrate this, I have used Adventureworks database AdventureWorksDW2008.   Query used is to find the sales amount in each sales territory for each product in every year. CREATE view [dbo].[v_Exceldashboard] AS       SELECT EnglishProductName,                CalendarYear,                d.SalesTerritoryGroup,                SalesTerritoryRegion,                SalesAmount,SalesAmount - TotalProductCost as profit ,                EnglishMonthName       FROM dbo.FactInternetSales a      INNER JOIN dbo.DimProduct b  ON a.ProductKey = b.ProductKey      INNER JOIN dbo.DimDate c ON a.OrderDateKey = c.DateKey      INNER JOIN dbo.DimSalesTerritory d ON a.SalesTerritoryKey = d.SalesTerritoryKey Connect Excel to this database and retreive the result of the above query into one sheet and we can create pivot data out of this result set as shown in Fig 01. Fig 01