Forecasting Exchange Rates Using R Time Series

Time Series is the historical representation of data points collected at periodic intervals of time. Statistical tools like R use forecasting models to analyse historical time series data to predict future values with reasonable accuracy. In this post I will be using R time series to forecast the exchange rate of Australian dollar using daily closing rate of the dollar collected over a period of two years. Continue reading

SSIS: Creating Dynamic Data-driven SSAS Partitions

Partitions improve the query response of SSAS cube by narrowing the MDX query to a pre-defined subset of data. Usually cubes are developed with few basic partitions to begin with and new partitions are added over time. Partitions are created using XMLA commands. The rest of this post explains the steps to create partitions using metadata and SSIS package. Continue Reading

SSIS: Dynamic Processing of SSAS Partitions

SSAS Partitions are primarily geared towards better query performance by narrowing the MDX query to a pre-defined subset of data. Each partition must be in processed state for the cube to be available for browsing. However in a delta ETL run, it is more than likely that only some partitions would be refreshed with data while the rest remain unchanged. For example in a hospital admissions cube partitioned by month, data movements are frequent for admissions made during the current/last month compared to earlier months. This means if the partitions updated by the ETL run can be identified, then we can tailor SSIS package to dynamically process only those partitions that require data refresh while leaving the remaining partitions untouched. Continue Reading

SSIS Package to Process SSAS Cube

The execution plan of SSAS processing engine triggers a sequence of processing tasks based on the object’s current processed state and its dependencies with other objects. This is what happens when the SSAS cube is processed from SQL Server Management Studio (SSMS). It is possible to replicate this execution plan close enough in an SSIS package so that the cube is always processed and refreshed with latest data.

Continue Reading

SSRS: Why does SSAS Member UniqueName Return Nothing?

If your SSRS report is using SSAS MDX datasets, the member uniquename is particularly useful to pass parameter values to drill-through MDX reports or for sorting based on key values. If Fields!Attribute.UniqueName is returning nothing or blank or null value in your report, then most likely you are missing the DIMENSION PROPERTIES clause in your MDX dataset query
Continue Reading

SSAS: Using MDX to Calculate Snapshot Measures

In a data warehouse, Periodic Snapshot table is a type of fact table that “freezes” the measure value at recurring reporting periods. This enables reporting of measures AS AT a point in time. Periodic Snapshots are usually created from Transaction Fact tables that support reporting FOR the period. Using MDX it is possible to create snapshot values for measures from transaction fact without creating Periodic Snapshot tables. Continue Reading

SSIS: ADO.NET Destination in Bulk Insert Mode and Foreign Keys

Bulk Insert option for SSIS ADO.NET Destination which is available since SQL Server 2008 R2 improves data load speeds significantly. This option is enabled on the ADO.NET Destination component by selecting the “Use Bulk Insert when possible” check-box Continue Reading