In last post I’ve created a columnstore index on dbo.Post table. As you remember original table size was 121 GB, and clustered columnstore index space was 32.5 GB. Columnstore indexes are perfect for analysing data with better performance. What we can do to reduce index size?

At first we can remove unnecessary columns.

Assume that our object is a fact table in DW. In most cases you don’t need Id column. So we can remove it. Second one is Body and Title column. Such type of information usually aren’t used in analitycal computations.

ALTER TABLE dbo.Posts_Columnstore
DROP COLUMN [Id]
ALTER TABLE dbo.Posts_Columnstore
DROP COLUMN [Body]
ALTER TABLE dbo.Posts_Columnstore …

Today, I want to show you how to create columnstore index and its performance.

Our example is Posts table from StackOverfolw Database. It contains 46.947.633 rows and takes 121 GB of space.

There are a lot of cases when you need move your databse data from one place to another. When it comes to move small amount of data. This operation is pretty simple. But when you want to copy, for example >10 000 000 rows. You might encounquer problems with RAM capacity.

We will copy data between two SQL Server tables: dbo.Badges_Source and dbo.Badges_Destination. Both tables are based on dbo.Badges table from StackOverflow database. We will use python and pyodbc package. Code on my github repository:

When you developing Tabular Models, you can use DMV views to collect detailed information about your models.

Queries are based on SQL language, but on very basic level. For example you cannot use joins, grouping, some functions, etc.

For example, if you want to gather informations about cardinality of every column in your model. You might use:

SELECT * FROM $SYSTEM.DISCOVER_STORAGE_TABLES WHERE LEFT(TABLE_ID, 2) = ‘H$’

Today I want to show you how you can use Power BI to analyse your projects statistics.

We can do it directly on a website. For example, when we create our own query, in this case I’ve created “All items” query.

When it comes to Python or R. You can calculate correlation coefficient pretty easy. But, how to do it in Power BI?

We will be looking for a correlation coefficient for “Reputation” and “Score” columns in a StackOverflow database.

Here is the data structure.

When you want to get value from previous day, the firs idea is PREVIOUSDAY() DAX function. But there is more options.

Entire code used in this post is in Power BI example bellow in “Comparioson” tab.

Surce dataset is Coovid-19 dataset from Kaggle.

Sometimes you need to retrieve data from SSAS service but you dont want to use application like Excel or Power BI. You can do it with Python.

We will connect to local SSAS instance.