Welcome in Dinesh Patel Blog

BI Concept:  Star Schema: The star schema has a center, represented by a fact table, and the points of the star, represented by the dimension tables. Snowflake Schema: In a snowflake, the dimension tables are normalized. ODS: ODS should be considered only when information retrieval requirements are to be real time and cannot access OLTP systems because of criticality of resources. Dimension Types: Changing Dimension:  Changing dimension is the dimension which has at least one attribute whose value would change over the time. Slowly Changing Dimensions: Attributes of a dimension that would undergo changes over time. For example: Employee work location  • Type I: The existing record is updated with change. • Type II: History is preserved, a new record is added to dimension and old record is marked as inactive.  EndDate or IsLatest is generally used to represent latest record of a business key. • Type III: Type III preserve...

Dimensions and Measure Groups can be related in different ways Regular - A Direct relationship.  Key column for the dimension is joined directly to the fact table Fact - A Column in the fact table can be treated as a  degenerate dimensions  (usually used for drillthrough).  Useful dimensional data is sometimes stored in a fact table to reduce duplication. Referenced - A Dimension joined through another dimension is treated as if directly joined to the fact table.  Key column for the dimension is joined indirectly to the fact table through a key in another dimension table Many-to-Many - Handles many-to-many fact relationships. I t is frequently useful to join a single fact to multiple dimension members.

Usage-Based Optimization is a fantastic feature in SSAS to improve the most frequently used MDX queries of any cube database. This feature is used to design aggregations based on actual user queries using Business Intelligence Development Studio. But when ever you try to design  Usage-Based Optimization  you might have seen all the options  disabled.  This uses the technique of creating aggregations on FREQUENTLY USED queries in the given time interval.  We can enable the options in Usage-Based Optimization wizard. Connect to SSAS using SSMS (Sql Server Management Studio). Right click on the server and select properties as shown below. set CreateQueryLogTable to  true. Set the value of  QueryLogConnectionString  to any database where you wish to store your queries. Set the value of  QueryLogSampling  to the appropriate value for your environment (the default value of 10 means every 10th query will be logged). The lowe...

MOLAP - The partition storage mode is standard MOLAP. Proactive caching is disabled. You need to process the partition to refresh data. Scheduled MOLAP - Same as MOLAP except that the server will process the partition on a daily schedule. Automatic MOLAP - The default silence interval is set to 10 seconds. As a result, the server will not react if the data change batches are fewer than 10 seconds apart. If there is not a period of silence, the server will start processing the cache in 10 minutes. Medium-latency MOLAP - Similar to Automatic MOLAP except that the MOLAP cache expires in 4 hours. Low-latency MOLAP - The MOLAP cache expires in 30 minutes.   Real-time HOLAP - As with standard HOLAP, partition data is stored in the relational database, and aggregations are stored in the cube. Aggregations are rebuilt as soon as a data change notification is received. Real-time ROLAP - As with standard ROLAP, partition data and aggressions are stored in the ...

To create a model for dynamic security: 1. Create a two-column security table in the relational data source. In the first column, specify the list of Windows user names or custom data values. In the second column, specify the data values that you want to allow users to access for a column in a given table. 2. Import the security table into the tabular model using the Import Wizard. 3. Using the Import Wizard, create a one column table that contains the user names or custom data values for the model. The values in this table must be unique. This table does not need to be materialized in the relational data source; instead, it can be constructed in the Import Wizard by querying the security table for the unique user names or custom data values. 4. Create a relationship between the security table and the column that is being secured. 5. Create a relationship between the bridge table created in step 3 and the security table created in step 2. 6. [optio...