SSAS MDX and TSQL

OLAP allows users to discovery new insights using BI applications like Business Objects and Microsoft SSRRS and SSAS.  MOLAP  traditionally can be implemented as cube  but newer technologies has made it possible to design MOLAP in a  tubular format similar to pivot tables with the introduction of SSAS 2012 which is much easier.

MDX queries can be called within Stored Procedures, User-defined Functions and Views using linked server. This gives the user the ability to manage the output in various format. Users  without knowledge of MDX queries  can utilized these database objects for various reports.  Learning  to write MDX queries can be hard for non technical experts.

The link server has to be created first in the process between the host SQL Database and SSAS Cube. For this demonstration SQL Server 2008 is being utilized.



Normal MDX query




Notice the difference between MDX and regular TSQL or SQL Statements. You have to know the hierarchy and how they are organized base on the subjects and dimensions.  To add the filter you have to write a different statement within the From section.  I am still learning more about the MDX  and SSAS.  Implementing MDX with regular SQL statements to create regular database objects eliminates the need to learn MDX queries. Since most vendors offers a link type technology  between their host platform and SQL Server it would make the connection to SSAS much easier.

View


  


Once the view has been created   you can now query the database.






Additional filter can be implemented to restrict the flow of data.





User-defined Function 

The first UDF which is table based  has no parameters to filter the data.  I decided to use open query format to make it easier to read the script. A where filter can be included to restrict the flow of data.








I have included the where filter to restrict the data to a specific country.




The same UDF has been modified to include parameters which allows the user to filter the data based on input parameters added to the where section of the query.






Stored Procedures






Reports

I would utilized the view created earlier to create report using Microsoft Excel power pivot. 



In the next report i would utilize the Reseller_Sales_udf to create reports from Excel.






XML

SQL Server like many other vendor has XML embedded to produce XML styles.












Comments

Post a Comment

Popular posts from this blog

ETL ( Automation vs Manual )

ETL applications like Talend, SSIS and Pentaho can simplify ETL or Data migration by eliminating or reducing the amount of SQL coding needed to complete the process. However same functions performed by ETL applications can be performed by scripting using various dialect of SQL and programming language like C# or Java. The downside of scripting manually without an ETL application is the need of connecting to data source and destination source. However most vendors like Microsoft SQL Server provides developers the option through linked server using SQL Server Management Studio or Oracle MySQL Workbench for MySQL.  Depending on the application a third programming language is need as interface like Java or C++ when performing the process from a developmental application. This will allow both the source and destination sources to be linked.  Another problem with the manual process is the complexity of SQL coding needed which needs to be altered constantly and the length of the co...
Read more

Data Migration

Migrating data  can be a challenging  task depending on the size of the data, platform and systems involved with the migration process. The age or the nature of data can also add to the complexity of the migration process. Legacy data or old data needs to be re-validated and cleaned to prevent any major issues when the data has been migrated just like you have with Data Warehouse system. ETL applications like Talend , Pentaho and Microsoft SSIS can be utilized in this process. The structure of the data plays a major role in determining the schema and the layer of the data.Texted based systems and RDBMS have different properties that needs to be examined and changed. The final properties to be examined is the platform. Migrating data from SQL Server 2008 to SQL Server 2012 is much easier than migrating from SQL Server 2008 to Oracle 12c or 11g due to different datatypes, objects , schema and properties. In some cases the underlying objects like ...
Read more

SSIS, SSAS & Stored Procedure

Image
In the following series SSIS would be utilized to extract data from Stored Procedure which contain query that is linked with SSAS MDX query via linked Server. Talend requires additional components while SSIS has fewer components but the output is the same. First process in extracting the query through SSAS via Linked Server is to create a linked server. Linked Server Second process is to create the stored procedure to extract the data. Stored Procedure definition with the MDX and regular SQL qeury Third process is to create an SSIS package. In the Control Flow component you need  two task which the Execute SQL Task and Data Flow task.  The fourth step is to create the database connection to the source and destination systems. In this case only one database system is being utilized. Another big difference between SSIS and Talend is that Talend  works well with JDBC but JDBC is allowed in SSIS rather .NET, OLED, ODBC etc which makes t...
Read more