SQL Server 2012: Multidimensional vs tabular

which model should you use, Multidimensional vs Tabular?

Well, there is no clear-cut answer, but there are some factors that can make you choose one over the other:


  • If you want to use DAX, you have to use Tabular
  • If you want to use Power View, you have to use Tabular
  • If your dataset is extremely large, go with Multidimensional
  • If you need writeback support, you have to use Multidimensional
  • If you need access to many different external data sources, choose Tabular
  • If you need complex calculations, scoping, and named sets, choose Multidimensional
  • If you need extreme speed and consistently fast query time, choose Tabular
  • If you need Many-to-Many relationships, choose Multidimensional (can be done in Tabular but difficult)
  • If your solution requires complex modeling, choose Multidimensional
  • If you need any of the following features, you must use Multidimensional: Actions, Custom Assemblies, Custom Rollups, Custom Drillthrough Actions (but BIDS Helper adds support for actions in a PivotTable in Excel but not in PerformancePoint), Linked objects, or Translations.


I would recommend going with Tabular if possible, as it is better to use for these reasons:

It uses your existing relational model, so there is usually no need to create a star schema (which usually means using ETL to create new dimension and fact tables in a Data Mart or Data Warehouse).  Complex DAX may require a star schema
It uses DAX, which is much easier to use than MDX, and least for the basics (but mastering DAX and optimizing DAX is hard)
It uses xVelocity/Vertipaq, which is much faster than Multidimensional
It is faster to develop
It’s less expensive to use in terms of time, resources and skill requirement
You can extend the data model without reprocessing the whole database by using calculated columns.  Instead it requires a much faster “Process Recalc”
In situations where a multidimensional model requires the use of snapshots (i.e. quantity totals by day), tabular is better because it can avoid snapshots by making up-to-date calculations at query time (thanks to its speed because the data is in memory)
In situations where a multidimensional model requires a distinct count (i.e. how many new distinct customers this month), tabular is better because it stores data in a way that discount count is very fast (writing a measure vs changing the data model and reprocessing the data)
Keep in mind the option on creating both types of models against the same data warehouse.  Say you built a multidimensional model before tabular was released, but now want to use Power View.  You can always just build a tabular cube to allow for the use of Power View.  Also note that Tabular will only work if there is sufficient memory for the entire cube in memory, otherwise you won’t be able to open the database.

You can also use the tabular model to prototype in since it’s so much easier and quicker to use, and then decide later whether to convert it to a full-blown multidimensional model.  And because of its ease of use, the tabular model could also be used by power business users to prototype a cube in, and then later IT can take that model and enhance it in tabular or convert it to multidimensional (self-service BI at it’s best).





Comments

  1. UnknownOctober 31, 2016 at 7:31 AM

    naturally data warehouse is also a database. The records from multiple data sources (may be some other databases)are collected and stored in Denormalized manner. It normally stores months or years of data to support historical analysis

    ReplyDelete
  2. pridesysOctober 31, 2018 at 3:46 AM

    thanks for helpful post. Pridesys Business Intelligence (BI) is offering you easy-to-use analytics and business intelligence tools. With these tools you can explore your data. Create new patterns, rich visual and insights. for more info visit: http://pridesys.com/business-intelligence-and-analytics/

    ReplyDelete

Post a Comment

Popular posts from this blog

host

import pandas as pd from datetime import datetime import os # Example DataFrame df_final = pd.DataFrame({     'Name': ['Alice', 'Bob', 'Charlie'],     'Age': [25, 30, 35],     'City': ['New York', 'Los Angeles', 'Chicago'] }) # Generate today's date in 'YYYY-MM-DD' format today = datetime.now().strftime("%Y-%m-%d") # Create a dynamic filename filename = f"{today}_data.csv" # Network drive path (Windows) network_folder = r"\\network_drive\shared_folder" # Full path to save the file full_path = os.path.join(network_folder, filename) # Save DataFrame to CSV try:     df_final.to_csv(full_path, index=False)     print(f"File saved successfully to {full_path}") except Exception as e:     print(f"Error saving file: {e}")
Read more

Steps to create SSH key from git bash

Generating an SSH key in OpenSSH involves a few simple steps. Here’s how you can do it: 1. Open a Terminal If you're on Linux or macOS, you can use the built-in terminal. On Windows, you can use the Command Prompt, PowerShell, or Git Bash if you have Git installed. 2. Generate the SSH Key Pair Run the following command: bash Copy code ssh-keygen -t rsa -b 4096 -C "your_email@example.com" Here’s what each option means: -t rsa : Specifies the type of key to create, in this case, RSA. -b 4096 : Specifies the number of bits in the key, 4096 bits is a good choice for strong encryption. -C "your_email@example.com" : Adds a label to the key, usually your email address. 3. Choose a File Location After running the above command, you'll be prompted to choose a location to save the key. By default, it will be saved in ~/.ssh/id_rsa (on Linux or macOS) or in C:\Users\YourUserName\.ssh\id_rsa (on Windows). Press Enter to accept the default location, or specify a diff...
Read more

test

.. https://ubiquis.co.uk/loading-csv-files-with-pdi-metadata-injection-2/ -- Create a temporary table to demonstrate the solution CREATE TABLE YourTable ( ID INT, YourColumn VARCHAR(100) ); -- Insert sample data INSERT INTO YourTable (ID, YourColumn) VALUES (1, '1234_4567_abc'), (2, '789_012_def'), (3, 'xyz'); -- Declare variables for storing results DECLARE @firstPart VARCHAR(100), @secondPart VARCHAR(100); -- Iterate over rows in the table DECLARE @currentID INT = 1; DECLARE @rowCount INT = (SELECT COUNT(*) FROM YourTable); WHILE @currentID <= @rowCount BEGIN -- Retrieve input string from the table DECLARE @inputString VARCHAR(100); SELECT @inputString = YourColumn FROM YourTable WHERE ID = @currentID; -- Check if the delimiter exists in the input string IF CHARINDEX('_', @inputString) > 0 BEGIN -- Find the position of the first occurrence of the delimiter in the input string DECLARE @delimiter...
Read more