Knowledge Sharing

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

Microsoft has provided excellent feature in MS-Excel which enables the BI developer/Analyst to dig into the corporate data with different view to help in decision making. Microsoft introduced use of power pivot in MS-Excel 2010 and I would like to give some of the basic feature in this post. Excel can connect different database to pull the records and for this demo we can connect excel with the sample database AdventureworksDW2008 provided by Microsoft. I do not want to connect the tables directly so instead we can use the below query. select OrderQuantity,SalesAmount,Englishproductname ,c.englishmonthname,c.FiscalYear from dbo.FactInternetSales a inner join DimProduct b on a.ProductKey = b.ProductKey inner join DimDate c on a.OrderDateKey = c.DateKey Open Excel and connect to SQL by selecting From SQL Server option from From Other Sources of data menu as showned in the figure 0.1                       Fig 0.1