Multidimensional database and star schema

Dimensional modeling (DM) names a set of techniques and concepts used in data warehouse design. It is considered to be different from entity-relationship modeling (ER) and doesn’t necessarily involve a relational database. Dimensional modeling is a design technique for databases intended to support end-user queries in a data warehouse. It is oriented around understandability and performance.

A multidimensional database (MDB) is a type of database that is optimized for data warehouse and online analytical processing applications. Multidimensional database technology is a key factor in the interactive analysis of large amounts of data for decision-making purposes. Multi-dimensional databases are specially useful in sales and marketing applications that involve time series. Large volumes of sales and inventory data can be stored to ultimately be used for logistics and executive planning.

The most commonly used schema design in OLAP applications is STAR SCHEMA.
Star Schema is a relational database schema for representing multidimensional data. It is the simplest form of data warehouse schema that contains one or more dimensions and fact tables. It is called a star schema because the entity-relationship diagram between dimensions and fact tables resembles a star where one fact table is connected to multiple dimensions. The center of the star schema consists of a large fact table and it points towards the dimension tables. The advantage of star schema are slicing down, performance increase and easy understanding of data.

Read More

Writing better SQL in context of a programmer

Often developers / programmers mix object-oriented thinking with imperative thinking depending on their levels of skill, dogma and mood.
But when programmers write SQL, everything changes. SQL is a declarative language that is closer to natural language and has nothing to do with either object-oriented or imperative thinking. It is very easy to express something as a SQL query like we do in natural language. But it is not so easy to express it optimally or correctly with SQL.
Not only developers need to re-think their programming paradigm when writing SQL, they also need to think it in terms of mathematical set theory.
Writing Better SQL
Followings are the common mistakes that a developer makes when writing SQL queries in their programs.

1. NULL checking:
A novice developer’s first mistake is misunderstanding the NULL when they write SQL. Most of the time this misunderstanding is thinking that NULL is equals to NULL, which is not. This misunderstanding often lead to data discrepancy when joining tables.

2. Processing data using programming language
Only few good developers know SQL very well. Most of developers’ use JOIN, the odd UNION but not everyone uses window functions, grouping sets. A lot of programmers load SQL data into memory then transform the data into some appropriate collection type and do the nasty arithmetic operations on that collection using loops or verbose methods. But when one use SQL to do math it is simpler and it is faster or perform better.

3. Using WHERE Condition along with JOINS
Using WHERE condition on an INNER JOIN-ed set makes no sense, when you can possibly filter out your resulting dataset by using the same condition as Table joining condition. Which is again gain in performance when you have huge datasets.
This goes hand in hand with the point above – most people don’t know about the magic of the compound JOIN/ON clause. Just as with the WHERE clause, you can have multiple conditions in the ON clause of a table JOIN. By moving all relevant filtering to this ON clause (from the WHERE clause), you create smaller, intermediary cross products. This is a bit more advanced than anything else there, but if you just sit down and learn the JOIN/ON clauses, you will benefit quite easily from this.

4. Joining Tables Using Commas
This can/does create a very large cross product of the two tables before any filtering gets applied.

5. Using aggregate functions instead of window functions
Before the introduction of window functions, the only means to aggregate data in SQL was by using a GROUP BY clause along with aggregate functions in the projection. This works well in many cases, and if aggregation data needed to be enriched with regular data, the grouped query can be pushed down into a joined subquery. Very expensive operation!
Using window functions will, Lead to more readable SQL (less dedicated GROUP BY clauses in subqueries). Improve performance, as a RDBMS is likely to optimize window functions more easily.

6. Sorting data on memory
Doing operations with data on memory is always expensive.
If you sort any SQL data in memory, think again if you cannot push sorting into your database.
The SQL ORDER BY clause supports many types of expressions, including CASE statements, which can be very useful for sort indirections.

Read More

Dynamic Code Integration with .NET System.CodeDom

If we are building some sort of script executor/runner or SQL code executor we may need to have some dynamic code along with static codes. Such as if we have an SQL script executor which executes SQL from reading SQL script from a file (which may be serialized or can be read as a normal file), we may need dynamic code or data along with static code or data. For example consider the following SQL.

[code lang=”sql” title=”Script”]
"SELECT userid from dbo.user WHERE registration_date = ‘2014-08-4′;"

If we are running this script with our script executor on daily basis and we need to have “userid” of the users of current date what can we do? Consider the script goes directly to SQL query executor and there are some scenario we may need to change the script code as support task frequently.
.NET Code Document Object Model (CodeDOM) API or CodeDom compiler gives us ability to dynamically create / compile our code on run-time. and we can use .NET reflection to invoke methods on our compiled codes.
The following program is an example of upper scenario. We can have dynamic code on our script file and compiled it before sending to actual query executor. For example we can have following code

[code lang=”sql” title=”Script”]
"SELECT userid from dbo.user WHERE registration_date = \’"+DateTime.Now.ToString() +"\’;"

The code is pretty much self explaining what it does.

[code lang=”csharp” title=”Program”]
using System.IO;
using System;
using System.CodeDom.Compiler;
using System.Reflection;
using Microsoft.CSharp;

class Program
public static string EvaluateSourceCode(string source)
string result = "";

//prepare script
string src = "using System;\r\n\r\n" +
"namespace DataDynaCode\r\n" +
"{\r\n" +
" public sealed class Dyna\r\n" +
" {\r\n" +
" public string GetCompiledResult()\r\n" +
" {\r\n" +
" return " + source + ";" +
" }\r\n" +
" }\r\n" +

//compiler params
CompilerParameters compilerParams = new CompilerParameters();
compilerParams.GenerateInMemory = true;
compilerParams.TreatWarningsAsErrors = true;
compilerParams.GenerateExecutable = false;
compilerParams.CompilerOptions = "/optimize";

//compile and store results
CSharpCodeProvider cSharpCodeProvider = new CSharpCodeProvider();
CompilerResults compilerResults = cSharpCodeProvider.CompileAssemblyFromSource(compilerParams, src);

//invoke method using reflection and get output
if (!compilerResults.Errors.HasErrors)
Assembly assembly = compilerResults.CompiledAssembly;
Type classType = assembly.GetType("DataDynaCode.Dyna");
object objectInstance = Activator.CreateInstance(classType);
result = (string)classType.InvokeMember("GetCompiledResult", BindingFlags.InvokeMethod, null, objectInstance, null);

if (string.IsNullOrEmpty(result))
result = source;

return result;

public static void Main()
string code = "\"Now time: \" + DateTime.Now.ToString()";
string result = Program.EvaluateSourceCode(code);

Read More

ল্যামডা এক্সপ্রেশন সহজ পাঠ

ল্যামডা এস্প্রেশনকে অনেক সময় এননিমাস ফাংশন এর সাথে তুলনা করা হয় যদিও ল্যামডা এক্সপ্রেশন হল এমন ফাংশন যার কোন আইডেন্টিফায়ার নেই। এগুলো ইনলাইন অথবা সিঙ্গেল লাইন ফাংশন হয়ে থাকে। সি শার্পে => অপারেটর ল্যামডা এক্সপ্রেশনে ব্যাবহার করা হয়ে থাকে। এই => অপারেটর ল্যামডা এক্সপ্রেশন এর প্যারামিটার আর স্টেটমেন্ট বডিকে পৃথক করে।
একটা ল্যামডা এক্সপ্রেশন সাধারনত নিচের ফরম্যাট এর হয়
(ইনপুট প্যারামিটার) => { ফাংশন স্টেটমেন্ট / রিটার্ন  };
Parameter => Execution Codes.

নিচের কোডটা লক্ষ্য করা যাকঃ

//simple example of lambda expression.
public static void SimpleLambdaExpression()
List<int> numbers = new List<int>{1,2,3,4,5,6,7};
var evens = numbers.FindAll(n => n % 2 == 0);

এখানে  n => { return n % 2 ==0 একটি ল্যামডা এক্সপ্রেশন। যার Return একটা Boolean value আর যেখানে int n আমাদের ইনপুট প্যারামিটার। যার মানে Even Number  এর জন্য ফাংশনটি True রিটার্ন করে, Odd Number  এর জন্য False.



Read More

Variables in android strings.xml

Variables can be used in the strings defined in strings.xml using the c programming like printf  ‘%d’ syntax. This is because getString (int resId, Object… formatArgs) passes the string through Formatter.
First define a string in the strings.xml file (usually res/values/strings.xml).

<string name="unread_messages">You have %d unread messages.</string>

This string has a variable %d that will be replaced in the next step of our android java code.

String message = getString(R.string.unread_messages, 25);

In the example above, getString substitutes %d for the integer 25 in the string message.
Output will be: You have 25 unread messages.

Read More