MSBI - Business Intelligence

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 action...

Why Tabular? SQL Server Analysis Services is a solid and mature platform that now serves as the foundation for two different implementations.  Multidimensional models are especially suited for large volumes of dimensionally-structured data that have additive measure values that sum-up along related dimensional attributes & hierarchies.   By design, tabular architecture is more flexible than multidimensional in a number of scenarios.  Tabular also works well with dimensional data structures but also works well in cases where the structure of the data doesn’t resemble a traditional star or snowflake of fact and dimension tables.  When I started using PowerPivot and tabular SSAS projects, I insisted on transforming data into star schemas like I’ve always done before building a cube.  In many cases, I still do because it’s easier to design a predictable model that performs well and is easy for users to navigate.  A dimensional model has order and...

What is Data Warehousing? Data warehousing is the process of storing and managing data from varied sources to provide meaningful business insights. It involves data integration, cleaning, and transformation to support decision-making processes. What is Business Intelligence (BI)? Business Intelligence (BI) refers to technologies, applications, and practices for the collection, integration, analysis, and presentation of business information. It helps organizations make data-driven decisions. What is a Dimension Table? A dimension table is a table in a data warehouse that stores attributes or dimensions that describe the objects in a fact table. It provides context for measures in the fact table. What is Dimensional Modeling? Dimensional modeling is a design technique for organizing data in a data warehouse. It involves creating dimension tables and fact tables to provide a structure that is optimized for querying and analysis. What is a Fact Table? A fact table is a central table i...

CLR is abbreviation of Common Language Runtime. In SQL Server 2005 and later version of it database objects can be created which are created in CLR. Stored Procedures, Functions, Triggers can be coded in CLR. CLR is faster than T-SQL in many cases. CLR is mainly used to accomplish task which are not possible by T-SQL or can use lots of resources. CLR can be usually implemented where there is intense string operation, thread management or iteration methods which can be complicated for T-SQL. Implementing CLR provides more security to Extended Stored Procedure. Let us create one very simple CLR where we will print current system datetime. 1) Open Microsoft Visual Studio >> Click New Project >> Select Visual C# >> Database >> SQL Server Project 2) Either choose from existing database connection as reference or click on Add New Reference. In my example I have selected Add New Reference. 3) If you have selected existing reference skip to next step or a...