Archive

Archive for the ‘SQL 2012’ Category

FW: Slides from SSIS Performance Design Patterns | TechDays Hong Kong 2013

March 15, 2013 1 comment
Categories: BI, SQL 2012, SQL Integration Services Tags: ,

Custom Rollup in BISM tabular (PowerPivot)

As we know, the BISM tabular does not support custom rollup. However, I recently read the article here and found it is possible to achieve custom aggregation via DAX. It is not exactly the same as custom rollup with unary operator in MDX. But it at least can perform different calculation at different hierarchy level. Basically, you need nested Summarize statement to specify the table (calcuated, returned by DAX) to loop with. The drawback is that you have to maintain the DAX.

I wonder is it possible to add a few columns to the date dimension to specify the calculation logic for different level. For example, ‘DimDate'[YearCalculationUnaryOperator], ‘DimDate'[QtrCalculationUnaryOperator], ‘DimDate'[MonthCalculationUnaryOperator], ‘DimDate'[DayCalculationUnaryOperator]. And in the measure, use the DAX looks like:

if (
Count(‘Date'[Date])=1,
(    Switch(‘DimDate'[DayCalculationUnaryOperator],
‘+’, <Calculation expression for +>,
‘-‘, <Calculation expression for ->,
‘/’, <Calculation expression for />,
<default Calculation expression >)
),
if    (    Count(‘Date'[Month])=1,
(    Switch(‘DimDate'[MonthCalculationUnaryOperator],
‘+’, <Calculation expression for +>,
‘-‘, <Calculation expression for ->,
‘/’, <Calculation expression for />,
<default Calculation expression >)
)
, <default Calculation expression for other hierarchy>
)
)

Well, time to dig into the BISM

BI Image X: SQL 2012 RC0 and VMWare Workstation 8

February 24, 2012 3 comments

Microsoft recently released the BI Image X: SQL 2012 RC0 which allows us to have a look at the latest technology and data visualization on SQL 2012 platform. However, again, it requires Hyper-V. I use windows 7 and I don’t want to dual boot my laptop. Hence, I decided to use VMware workstation 8 to host this.

Here is what you need:
•    VMware Workstation 8
•    VHD (downloaded from http://www.microsoft.com/download/en/details.aspx?id=28802)
•    WinImage (http://www.winimage.com/download.htm )

Step by Steps:
1.    You need WinImage to convert your vhd into vmdk format. It will take quite long time. On my i7 laptop, it takes about half hour to do the conversion.
2.    Create a virtual adapter, shown as below (the one highlighted). Also read the “Prepare the Host Server” section on the Microsoft download page (http://www.microsoft.com/download/en/details.aspx?id=28802). Basically, you need to create a VMware virtual network adapter equivalent to the Hyper-V virtual network.

3.    Create a VM using wizard. I gave the VM 4 processors and 8G ram as recommended by Microsoft.
4.    Use the existing virtual disk (vmdk)
5.    Start the VM and configure the network settings. (Instruction on MS download page). Basically, you set the network setting as the following:
•    IP Address: 192.168.150.1
•    Subnet mask: 255.255.255.0
•    DNS Server: 192.168.150.1
6.    To verify the configuration of the network settings, you can ping from you host via cmd: ping 192.168.150.1 as well as ping form within your VM via cmd: ping 192.168.150.1. Both commands should be successful.
7.    Rearm the VM and Reboot VM as required
8.    Add external Network card to you VM. Use the “Bridged” one. After adding the network card, you should be able to see two NIC in your VM settings, shown as below:

9.    Now you should be able to ping Internet as well.
10.    Take a snapshot of your working VM.
11.    Enjoy the demo.