Knowledge Sharing

Today I explored the features of power pivot in MS-Excel and amazed with its wonderful capabilities. Even though at first glance it looks like the replica of its predecessor, power pivot has plenty of benefits attached to it. In this post I would like to show case the reason behind the powerful performance by power pivot, its installation and also small demo to understand the creation of basic pivot. Power pivot engine is known as Vertipag which is based on the in memory calculation. It can hold the entire data from the selected tables of the database which may go beyond millions of rows. Advantage of holding the entire data in memory is to avoide the expensive disk I/O which is the main reason for slow performance. Power pivot stores the data in a compressed mode to reduce the disk storage space. This we can understand it very clearly by zipping the MS-Excel power pivot file and find that there is no difference in size before and after the zipping. Another interesting feature i

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

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

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

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

In this post I would like to talk about two functions COUNTIF and SUMIF . these two functions will be extremely helpful for us to aggregate details very easily. see the examples given below to identify the use. Sheet value: Fig 1 Find the sum of sales for each sales person. We can use SUMIF function to accomblish this requirement. SUMIF expects three parameter as shown in figure 2. range is the total detailed area where the function has to search for the details, criteria is the value that should find in the given range and the sum range is the range of values to be aggregated for the selected criteria. Fig 2 Formula looks like this =SUMIF( D$2:E$10 , H$2 , E2:E10 ) and the result is Name Jithesh a b c Sales Amount 95274 476 545 4345 Same way, to find the number of sales done by the person, we can make use of the function countif, an inbuilt function of MS-Excel. It expects two parameter, first

OPENQUERY Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one. OPENQUERY does not accept variables for its arguments. OPENQUERY cannot be used to execute extended stored procedures on a linked server. However, an extended stored procedure can be executed on a linked server by using a four-part name Any call to OPENDATASOURCE, OPENQUERY, or OPENROWSET in the FROM clause is evaluated separately and independently from any call to these functions used as the target of the update, even if identical arguments are supplied to the two calls. In particular, filter or join conditions applied on the r

Getting error as showed in figure 1 when I tried to create new SSIS package.  Fig 1 This is due to msxml3.dll and msxml6.dll crash. we an reregister the dlls by using below commands in run ( start->run) regsvr32 msxml3.dll regsvr32 msxml6.dll

Options available for processing cube and dimensions are almost same. Cube is having couple of more options than dimension. Cube There are 7 different types of options are available for cube processing. Process Full – Server checks the storage mode and if the mode is MOLAP, server reads the from the relational data and store it in a compact format. If there were aggregations defined for the cube, the server will build those aggregations during these processing. Finally the server creates indexs for the data that helps the speed access to data during querying. Process default – Server checks all the objects and process only those that have not been processed if the cube data has been processed and if aggregations and indexes are not processed, then those are processed. Process incremental - Process only new fact data Unprocess – it will clear all the data in the cube that we have processed already. Process structure – Process all the cube dimensions and the

Most of the scenarios, there will be relation between different attributes of the same dimension and in this case we have to explictly define the relationship using dimenison designer. It improves the performance as well as affects calculations that are aggregated across these attributes. There is a chance of performance degrade if the relationship is not set properly. In this post we can see the steps to set attributes relationship and also important properties of the same. We can use the same DSV(AdventureworkdDW2008) that I used to demonstrate named query where I have replaced the below query with the actual DimProduct table. select a.ProductKey          ,a.EnglishProductName          ,b.ProductSubcategoryKey          ,EnglishProductSubcategoryName          ,c.ProductCategoryKey          ,EnglishProductCategoryName from dimProduct a inner join DimProductSubcategory b on a.ProductSubcategoryKey = b.ProductSubcategoryKey inner join DimProductCategory c on b.ProductCat

Named query in DSV is important in helping the developer to add only required field from the multiple tables. It is very similar to the views in database. There may be scenario where few fields required out of n number of fields from multiple tables for e.g. Only three fields are important for product sales and profitability analysis such as product name, product sub category and product category but these are spread across three tables called Product , productSubCcategory and productCategory along with so many unwanted fields (Refer the database AdventureWorksDW2008). Here we can make use of named query instead of referring these tables directly in DSV which improves the performance as well. I would like to give steps required to create named query in DSV in this post. Step 1: Create new DSV with FactInternetSales as main table and pull all the related tables to it and name it as InternetSales. Fig 1 Step 2: Right click on DimProduct dimension and select the

In Analysis services 2008 we can build cubes via three approaches top-down bottom-up empty cube Traditional way of building cubes is bottom-up from existing relational databases. In the bottom-up approach, you need a data source view from which a cube can be built. Cubes within a project can be built from a single DSV or from multiple DSVs. In the top-down approach, you create the cube and then generate the relational schema based on the cube design. In SSAS 2008 you also have the option to first create an empty cube and then add objects to it. Here I would like to demonstrate top-down approach. Schema datawarehouse is empty as shown in figure.  In this demo we will be designing the tables from SSAS and deploy it into this schema. Step 1 : Create a new SSAS project and you can see in Fig 2 that datasource, dsv, cube, dimension etc are empty. Step 2 : right click on the Cube and click on new cube. Choose the option Generate