Olap and Multidimensional Model

Most times used interchangeably, the terms Online Analytical Processing (OLAP) and data warehousing apply to decision support and business intelligence systems. OLAP systems help data warehouses to analyze the data effectively. The dimensional modeling in data warehousing primarily supports OLAP, which encompasses a greater category of business intelligence like relational database, data mining and report writing.

Many of the OLAP applications include sales reporting, marketing, business process management (BPM), forecasting, budgeting , creating finance reports and others. Each OLAP cube is presented through measures and dimensions. Measures refers to the numeric value categorized by dimensions. In below diagrams, dimensions are time, item type and courtiers/cities and the values inside them (605, 825, 14, 400) are measures.
The OLAP approach is used to analyze multidimensional data from multiple sources and perspectives. The three basic operations in OLAP are:

  • Roll-up (Consolidation).
  • Drill-down.
  • Slicing and dicing.
  • Roll-up or consolidation refers to data aggregation and computation in one or more dimensions. It is actually performed on an OLAP cube. For instance, the cube with cities is rolled up to countries to depict the data with respect to time (in quarters) and item (type).
    On the contrary, Drill-down operation helps users navigate through the data details. In the above example, drilling down enables users to analyze data in the three months of the first quarter separately. The data is divided with respect to cities, months (time) and item (type)

    Slicing is an OLAP feature that allows taking out a portion of the OLAP cube to view specific data. For instance, in the above diagram, the cube is sliced to a two dimensional view showing Item(types) with respect to Quadrant (time). The location dimension is skipped here. In dicing, users can analyze data from different viewpoints. In the above diagram, the users create a sub cube and chose to view data for two Item types and two locations in two quadrants.

    OLAP systems are mainly classified into three :

  • MOLAP (Multi-dimensional OLAP)
  • ROLAP (Relational OLAP) : works with relational databases
  • HOLAP (Hybrid OLAP): database divides data between relational and specialized storage
  • DAX Measure Dependencies in SSAS Tabular and Power BI

    27-April-2019

    This method is fairly simple, you just need to run the following DMV on top of your SSAS Tabular model or your Power BI Desktop file and Import the results in Power BI.

    SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY

    For Power BI you’ll need to find the local port number then you’re good to go. The only part that might not look very straightforward at first, would be finding the database in Power BI Desktop model.

    An easy way, after you find the local port number of an opened Power BI Desktop file, is to find the database name from SQL Server Management Studio (SSMS) when connecting to the Power BI Desktop model:

    • Open SSMS
    • Select “Analysis Services” as “Server Type”
    • Type in “localhost:PORT_NUMBER” as “Server Name” then click “Connect”
    • Open Power BI Desktop
    • Select “SQL Server Analysis Services database” then click “Connect”
    • Enter the “Server” and “Database”
    • Make sure you select the “Import” mode
    • Expand “MDX or DAX Query” and copy/paste the following DMV then click OK
    SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY
    • After importing data to Power BI click “Edit Queries” to open “Power Query Editor”
    • In Query Editor rename columns with more user friendly names
    • I also do prefer to capitalize each word in the “Object Type” and “Referenced Object Type” columns. You can do this by selecting both columns then right click and select “Capitalize each Word” from “Transform” sub-menu

     


    WE ALWAYS WORK WITH :