From SQL to Spark

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

Visual Insight charts inside Microstrategy documents

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

Metric calculation based on selection in Microstrategy

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

What I have learned in the name of unit testing of ETL, DWH, OLAP Cube and MDX

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

5 Tips to avoid running out of space in SQL Server Database

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

Ceiling in MDX

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

Pre order, Post order, Level order (BFS) traversing with 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

Leaf members do not sum up to parent – Part 2 (parent child hierarchy)

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)