In my previous post called “How to drop tables database wide using T-SQL” I’ve explained that how we could delete all tables database wide in SQL Server. But, what if there are some other schemas other than “dbo” and some tables that are created in those schemas? If we just delete “[dbo].” from the code we will obviously face to the following error message: “Cannot find the object “TABLE_NAME” because it does not exist or you do not have permissions.” for those tables that are not under “dbo” schema. So, we need to retrieve all schemas and their related tables. This approach is really helpful when we are implementing an ETL process that is designed to delete the tables from particular schemas and recreate the tables and populate new data. This is a common way for staging to delete and recreate tables instead of updating existing tables as update is really costly.
So, let’s write some codes.
Continue reading Drop tables from selected schemas database wide
There are some cases that we need to drop all tables database wide. For example when we have a test database and we need to delete all tables from the database and recreate some other tables for testing we need to drop each table manually using the GUI. Assume we have 50 tables in the database, so, we need to drop the tables one-by-one and repeat the process 50 times. Actually it is getting harder when there are some relationships between the tables!
So, what should we do now? Okay, we need to drop all dependencies first and the drop all 50 tables! It’s frustrating, isn’t it?
Continue reading How to drop tables database wide using T-SQL
SSAS 2012 supports three different approaches for creating a BISM (Business Intelligence Semantic Model):
a. Uses relational modelling constructs like such as tables and relationships
b. Uses xVelocity in-memory analytics engine for sorting and data calculations
c. Needs to use SSDT (SQL Server Data Tools) to implement
d. Can import data from relational data sources using OLE DB native and managed providers
e. Tabular solutions only support one model.bim file per solution, which means that all work must be done in a single file. Development teams that are accustomed to working with multiple projects in a single solution might need to revise how they work when building a shared tabular solution.
f. support DAX calculations, DAX queries, and MDX queries
g. Tabular model databases can use row-level security, using role-based permissions in Analysis Services (DAX implementation required)
h. It might be not a good choice for the systems that are going to load terabytes of data
Continue reading Three different approaches for creating a BISM in SSAS 2012, Tabular vs. Multidimensional vs. PowerPivot