SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Columnstore Index Data Types

The Voice of the DBA

Daily Coping Tip

Find three reasons to be hopeful about the rest of the year

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

Are Cubes Dead?

I was talking with a friend recently about technology. This individual is a person focused on business intelligence, originally a developer, but now an architect and consultant. They have a fair number of clients and have worked with them to build solutions to assist in analysis and decision-making for all sorts of organizations. This person has primarily worked in the Microsoft stack but has embraced NoSQL, Hadoop, and other technologies. In many ways they view the world as I do, using what works well for a particular situation without prejudice. They want to be effective, using whatever technology may be best in the current situation.

In their career, this person has extensive SQL Server Analysis Services experience and has built many cubes over the years that clients access with any number of front-end tools. I would guess that cube design and construction have made this person a lot of money over the years.

As we talked, I wasn't surprised to hear my friend say they thought cubes were dead. It was an approach to analysis that they wouldn't recommend anymore. That is something I've felt for some time. As data volumes grow and competition increases, there is a need for more real-time analysis. The processing time for cubes doesn't make sense.

Hardware advances, query technologies against files in data lakes, and automatic ingestion of large volumes of data into columnar formats have reduced the need for data mart cubes. I see less and less content produced in this area, both by vendors and individuals working with technology. ETL has given way to ELT, and data lakes seem to be far more useful than data marts that pre-aggregate data in predefined ways.

Most of you reading this work in the OLTP space, but there are plenty of you that built BI solutions or interact with those that need them. In the modern, 2020s era, do you find people still building new cubes and taking advantage of ROLAP/MOLAP/HOLAP systems? Or is this now legacy tech you can't wait to remove from your infrastructure? I think BI is more important than ever, but cubes are dead.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Featured Contents

How to grant PowerBI Service Workspace access using PowerShell

glyn.jones1 from SQLServerCentral

Learn how to more easily manage permissions in the Power BI service using PowerShell.

Working with MySQL stored functions

Additional Articles from SimpleTalk

Stored functions in MySQL return a scalar value and can be used in a SQL statement. In this article, Robert Sheldon explains how to create and use a MySQL stored function.

Database Testing in a Flyway Development

Additional Articles from Redgate

If you can test and evaluate databases, and database objects, at every phase of the database development lifecycle, then you are much more likely to be able to adopt continuous delivery. The further down the delivery pipeline that bugs appear, the more costly in time and resources they are to fix.

From the SQL Server Central Blogs - Azure Synapse Explained

Joyful Craftsmen from Joyful Craftsmen Blog

In this article we would like to give you a high level summary on Azure Synapse.
What it is, what are its strengths and weaknesses observed through our project implementations...

From the SQL Server Central Blogs - Power BI Adding Dynamic Hierarchies – XMLA, TOM, C#

DataOnWheels from DataOnWheels

This post is a continuation of my adventure into the Tabular Object Model and how we can use it to make Power BI scalable and incorporate it into existing...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Columnstore Index Data Types
Which of these data types cannot be included in a nonclustered columnstore index? (select all that apply)
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Changing the TDE Encryption

I have a SQL Server 2019 database protected with TDE. I want to change the algorithm used by the DEK from TRIPLE_DES_3KEY to AES_256. How can I do this easily?

Answer: Turn off TDE and re-enable with with a new DEK

Explanation: While you can turn off TDE and turn it back on with a new DEK, this isn't the easy way. You can use the ALTER DATABASE ENCRYPTION KEY DDL to change the algorithm. This will require decryption and encrypting all data. Ref: ALTER DATABASE ENCRYPTION KEY - https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-encryption-key-transact-sql?view=sql-server-ver15

Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 - Administration

Unable to connect to named instance. - There is a server running SQL Server 2012 32 bit. This is the default instance. We can connect to this remotely. We installed sql server 2017 standard edition as new instance (64 bit) and named it as Dev2017 Issue :- I am able to connect to the named instance from within the server but not […]

SQL Server 2017 - Development

Powershell Step in SQL Job not returning full data - I created a sql job and have multiple powershell steps. Powershell step runs query on databases hosted on different servers. This has always worked for me and able to query databases and export data to csv files. This is first time I am running into an issue where it will only export 1 row and […]

SQL Server 2016 - Administration

Linked Servers Role - Hello, I have been asked a question which i am struggling to find information online. When you set up a linked server does the user referenced in a linked server automatically gets the db_ddladmin role, if not what role does it get? Thanks in advance.

SQL Server 2019 - Administration

SQL Server replication migration from sql 2012 to 2019 - Hello I need to migrate existing sql server 2012 replication to the SQL Server 2019 instance. Can anyone please help with a robust scripts to extract all the publishers and subscribers to run against the new environment (sql 2019) and getting the replication established properly?

Failed code dcexec -u -s 3 -i "$(ESCAPE_DQUOTE(MACH))\$(ESCAPE_DQUOTE(INST))" - Hello, I cant understand what this code is doing, can some one please help me. dcexec -u -s 3 -i "$(ESCAPE_DQUOTE(MACH))\$(ESCAPE_DQUOTE(INST))"

Issues with configuring audit of sensitive data groups in SQL Server - I'm attempting to configure the "sensitive" versions of the batch completed audit groups (SENSITIVE_BATCH_STARTED_GROUP/SENSITIVE_BATCH_COMPLETED_GROUP) that are listed in the doc for SQL 2019 https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver15 I have set up all the data classifications and now would like to set up the associated auditing. Issue is I can't select them in the UI (they're not in the […]

PBALP User in SQL Server - Hi Everyone, Currently looking at tidying up some SQL servers and i have a number of local users (PBALP) being one of them i would like to drop. Is there a way i can check to see what if anything is dependant on that user?

SQL Server 2019 - Development

How to get weekly and Monthly view from daily in sql server - Hello All, I have a table like below. I need to get daily,weekly and monthly calls data from this. My out put would be like below Could any one please help what is the query to write here

Row wise sum based on condition - Hi All, I have an input like below. mytable: id | name | no -------------- 1 | A | 10 1 | A | 20 1 | A | 40 2 | B | 20 2 | B | 20 1 | C | 20 1 | C | 23 3 | D | 15 […]

why execution plan high cost on clustered index scan and how to mimize cost of c - I work on sql server 2019 i have clustered index scan 98 percent how to minimize it please i have execution plean have high cost on clustered index scan as https://www.brentozar.com/pastetheplan/?id=HkpoQtlwq table i have issue on it CREATE TABLE [Parts].[FMDMaster]( [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [ChemicalID] [int] NULL, [HashSubstance] [nvarchar](3500) NULL, [HashMass] […]

slow execution WHEN update chemical id on table chemicalhash so how to enhance i - I work on sql server 2017 i need update chemical id on table chemicalhash based on 3 columns compare to table fmd chemical compare 3 columns strSubstances,strMass,strCASNumber from table chemicalhash with 3 columns strSubstances,strMass,strCASNumber from table fmdchemical to get chemical id but issue slow so how to enhance it CREATE TABLE [dbo].[chemicalhash]( [id] [int] […]

Retrieve last 12 months - I have this code in mssql that i want to exclude de value 0 when it appears, so don't bug the average value of the query, also the query return this even in days like the one used n the code below, how can in fix both of the problems the return of the date […]

Amazon AWS and other cloud vendors

AWS Beginner questions - I'm having some trouble wrapping my head around the relationship between EC2 instances and EBS storage/S3 storage. From what I've read, it seems like EC2 instances tend to be initialized with EBS storage (which is charged per hour/GB). On the other hand, guides also recommend that, after setting up my instance with all the packages […]

General Cloud Computing Questions

Options to access/modify cloud data? - Hi, Novice here, so please forgive my simple question. I have previously run Microsoft SQL server. I then ran a Windows service to access and manipulate that data based on business logic. For example "view all records in a particular table, then update a field/col, then copy them to another table, then delete them". Repeat […]

Integration Services

EXECUTE SSIS Pacakge from command line with environment variables - hi, I am trying to execute ssis package from command line. I also tried to pass environmnet variables like below. Not sure what is wrong? Also is there a way that we can execute package using sql? if so how? example: Command line : dtexec/ISServer "\SSISDB\Folder\Foldername\"Packagename" /Server "Servername" /ENVREFERENCE "Environmentname foldername" This above command is […]

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.