When I started learning spark, I decided to learn it from the other way round. That means, rather than going through a step by step journey, I decided to learn it from BI reporting perspective. I took a table with some data. Then, I tried to answer some common reporting queries by using python+Spark. It … More From SQL to Spark
Microstrategy visual insight has lots of exciting beautiful looking charts. But many of them are not available in documents. You can import them into document following the steps described below. But, do not do this unless you really have to do it. Because, those imported charts become difficult to maintain inside document. most of the … More Visual Insight charts inside Microstrategy documents
Hello guys, I am back after a long time. I was learning Microstrategy during last one year. So, here I am, writing something on mstr. Today, I am writing about a problem that almost every mstr developer faces. Let’s say, you have two base metrics: dollars and units. Now, you like to calculate Market Share … More Metric calculation based on selection in Microstrategy
In one of our projects, we ‘somehow’ decided that every part of the system should be ‘unit test’-ed . It was a classical ETL->DWH->Cube project where web dashboard was built to show BI reports from cube (using MDX as reporting language). Off course, unit testing was nothing new to our web developers. But, our data warehouse developers … More What I have learned in the name of unit testing of ETL, DWH, OLAP Cube and MDX
Recently I came across a situation where data was being delivered at different times for different countries and client wanted to see data for latest common month in Region/Continent level reports. Data arrive on monthly basis, but it arrives at different dates for different countries. So, at one point in our data warehouse, we have Dec-2014 … More Common Latest Period in SSAS Cube
This is a simple example of SCD (Type-2) in OLAP cube. We have a Dimension table for Employee and their Departments. This dimension is an SCD. One employee worked in different department over the course of time. EmpID is business key and SID is surrogate key. DimEmpolyee table We have another dimension called DimTime. … More Slowly Changing Dimension in SSAS Cube
Try to follow these tips. Each of these topics has been explained in different websites. So, I am not going to explain them here. 1. Design Design your database efficiently (normalization, use of foreign keys, getting rid of unnecessary tables, selecting appropriate data type etc..). Clean up your database before your go LIVE! 2. Estimate Data … More 5 Tips to avoid running out of space in SQL Server Database
It’s just a simple solution to find ceiling of a measure with help of some string operation. We have a measure called [Measures].[Marks]. Now, we define a calculated measure like below: CREATE MEMBER CURRENTCUBE.[Measures].[CeilingMarks] AS CInt(Mid(cstr([Measures].[Marks]),0,InStr(cstr([Measures].[Marks]),“.”)-1))+1, VISIBLE = 1 ; It works like this; Let’s say our number is 315.5, first it finds the decimal … More Ceiling in MDX
A tree or hierarchy of my sample cube is being shown below: BFS or Level Order Traversing: with member [Measures].[x] as [Dim Category].[Parent].currentmember.level.ordinal select order(([Dim Category].[Parent].members),[Measures].[x],BASC) on 1 , [Measures].[x] on 0 from SalesCube And, here goes the output: Pre-Order traversing: By default, traversing in MDX is always in pre-order. select [Dim Category].[Parent].members on 1 … More Pre order, Post order, Level order (BFS) traversing with MDX
In part 1, we saw user defined hierarchies. For parent child hierarchy, we can use Unary Operator Column. Let’s take the same example from Part1. We have a simple cube with two dimensions called DimCompany and DimCategory, and only one measure called Sales. DimCategory table looks like this: Id CategoryItems Parent 1 Phone 4 2 Tab 4 … More Leaf members do not sum up to parent – Part 2 (parent child hierarchy)
Zahid's blog on Data Engineering and BI 