Build an OLAP Cube in SSAS using an ADO.NET Data Provider (SQLServerCentral 2019-04-30)

 SQLServerCentral Newsletter for April 30, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Build an OLAP Cube in SSAS using an ADO.NET Data Provider]( - [Compressed Microsoft SQL Server Backups by Default]( - [Row Pattern Recognition in SQL]( - [From the SQL Server Central Blogs - Lengthen a Primary Key–#SQLNewBlogger]( - [From the SQL Server Central Blogs - Setup SQL Server Alert to get automatically notified of database blockings]( Featured Script - [Database physical location, size, disk used..etc]( The Voice of the DBA  Guest editorial: SQL Code Metrics This editorial was originally published on Feb 17, 2009. It is being re-published as Steve is at SQL in the City in London today. The world has changed a lot and there are better tools, like [SQL Prompt]( with code analysis to help with the issues discussed here. SQL has a wonderful way of teaching us humility. Just when we think we have it sussed, we realise that there are better ways of doing it. I’ve never found a routine that can’t be improved in some way; particularly in my own work. Writing good SQL code is a fascinating activity, a constant fight against hasty assumptions, outdated rules, and learned procedural programming practices. SQL is very different from any other type of programming. This truth of this was brought home to me sharply the other day whilst reading about ‘code smells’ and quality metrics in C#. How wonderful it would be, I thought, if we could use the same techniques in order to detect ‘smelly’ SQL code in a database system. All we’d need to do is to check on the complexity of the code, the number of lines of code in a procedure, the number of SQL Statements, and possibly the complexity of the execution plan. The only tool I’ve found that makes some of this available is [SQLTAC](. However, a lot more is attainable. For example, we could check for an over-reliance on cursors, the use of the * to insert into a table, the presence of SQL Statements that consistently take a long time to execute, code without comments or documentation, over-use of table variables, tables without a primary key, over-denormalized tables, and so on. It would be deeply satisfying to be able to produce a graphical representation of TSQL code quality, to wave in front of some poor programmer’s face when you’re checking his work! Just as quickly as the thought had occurred to me, however, I dismissed it. The trouble is that when I supervise a programmer who is learning SQL, I sometimes learn new ideas that come from a fresh mind being applied to old problems. The code may, in general, stink, but the new ideas are priceless. If we apply ‘quality metrics’ and ‘best practices’ without applying our own judgement and intuition, we run the risk of ossifying rules that are merely intended to be guides. Code metrics are a good servant but a very poor master. Phil Factor. Phil Factor [Join the debate, and respond to today's editorial on the forums](  [Redgate Webinars](  Featured Contents [Build an OLAP Cube in SSAS using an ADO.NET Data Provider]( jerodj from SQLServerCentral SQL Server Analysis Services (SSAS) is an analytical data engine used in decision support and business analytics. It provides enterprise-grade semantic data models for business reports and client applications, such as Power BI, Excel, Reporting Services reports, and other data visualization tools. When paired with ADO.NET data providers, you can create cubes from external data […] [Compressed Microsoft SQL Server Backups by Default]( Additional Articles from Database Journal Learn how to create default database compression for your SQL Server databases. [Row Pattern Recognition in SQL]( Additional Articles from SQLServerCentral Itzik Ben-Gan asks you to invest in SQL Server’s future by voting for Row Pattern Recognition, a potential T-SQL syntax extension he deems the next step in the evolution of window functions. From the SQL Server Central Blogs - [Lengthen a Primary Key–#SQLNewBlogger]( Steve Jones - SSC Editor from SQLServerCentral Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. I saw a post recently where someone needed… From the SQL Server Central Blogs - [Setup SQL Server Alert to get automatically notified of database blockings]( SQLPals from SQLServerCentral Suppose you want to or need to know anytime your SQL Server is experiencing blocking where queries are being blocked and it may or may not escalate to a…   Question of the Day Today's question (by Steve Jones - SSC Editor):  Ignoring Divide by Zero Which setting do I turn on to allow an error message from a divide by zero to be ignored? Think you know the answer? [Click here]( and find out if you are right.  [Redgate SQL Prompt](    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Azure SQL Database User I have a user, Tiger, that exists in my Azure Active Directory. The domain is PGA. How do I add this user in my Azure SQL Database? Answer: CREATE USER [PGA\Tiger] FROM EXTERNAL PROVIDER Explanation: To create a user based on an Azure Active Directory user, we use the CREATE USER [domainuser] FROM EXTERNAL PROVIDER. Ref: CREATE USER - [Discuss this question and answer on the forums](  Featured Script [Database physical location, size, disk used..etc]( angelrapallo 90775 from SQLServerCentral Get some information about your database files. -- -- get all databases sizes -- written by: angel rapallo on 04/25/2019 -- -- f.size is the number of 8K pages in the database -- so there are f.size * 8 * 1024 bytes in the database -- the rest is pure conversion to mega/gyga/ etc.. -- -- there are 1048576 bytes in a megabyte -- there are 1073741824 bytes in a gigabyte -- -- total_bytes-available_bytes = used bytes and / total_bytes gives -- the percentage used of disk -- select d.name, f.name as filetype, f.physical_name as physicalfile, f.state_desc as onlinestatus, f.size * 8.00 * 1024.00 as bytes, cast((f.size * 8.00 * 1024.00) / 1048576.00 as numeric (18,2)) as megabytes, cast((f.size * 8.00 * 1024.00) / 1073741824.00 as numeric(18,2)) as gigabytes, cast(cast(v.total_bytes - v.available_bytes as float) / cast(v.total_bytes as float) * 100 as numeric(18,2)) used_disk_percent from sys.master_files f inner join sys.databases d on d.database_id = f.database_id cross apply sys.dm_os_volume_stats(f.database_id, f.file_id) v order by d.name [More »](  Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- [Post from the future]( - I haven’t seen anything to say otherwise, and I post I made earlier suggest the same, but all post times are currently displayed in UTC (I suspect this post will show it was made at around 08:40 on 28 April 2019). This post, however, appears to have come from the future, as it’s going to […] [Get Records from UDT]( - I’m trying to get data from User-Defined Table type which has the values needs to be inserted into Parent & Child table. In the below-stored procedure, I’m using separate SELECT statement for getting values from User Defined Table and inserting into Parent & Child table. Can you please help me with the following questions? 1) […] [Possible to attach an image in base64 to an email using database mail?]( - I cannot for the life of me get this to work….my script is below. I’m getting a base64 string from an api call that I need to turn into an image attachment. Trying to see if it is possible to do this using purely database mail. I know that’s not the best way to accomplish the task […] [Export results of a Stored Procedure to a PC Folder]( - Hi Forum, I have a Stored Procedure that selects one record from a table. I want to select one row at a time (maybe using a cursor?) and paste into a Folder on my PC’s ‘C’ Drive. I have below the code to create the Stored Proc & then the script to run it. This […] [Status Update 26 Apr 2019]( - Another slow day. New developer trying things, some not working. Old developer out with back issues. A few other things moved to test and got kicked back and fixed, but no real changes. Filed 3 new issues. 1 frontend high priority, 2 frontend low backlog. Hopefully things will pick up speed next week. [SSIS connection manager to a MySQL database]( - Hello, Looking for some help with creating an SSIS connection manager to a MySQL database. I have tried the following with no luck: The error message is: Test connection failed because of an error in initializing provider. Authentication to host ” for user ” using method ‘caching_sha2_password’ failed with message: Access denied for user […] [ADFS database [dbo].[IdentityServerNotificationCleanup]]( - There are a couple of adfs servers (pri and sec) with backend adfs database. This was installed by an ex-employee and that user is the database owner (account does not exist in AD anymore). However, the ADFS service runs on a service account and that service account also owns the schema for IdentityServerPolicy in database […] [SQLCMD Mode]( - Hi, I’m just curious. I know when we as database administrators and developers install SSMS on a new machine, we either import or set our preferences. I know there’s 10 or so that I set. One thing I’m curious about is whether you enable SQLCMD Mode to be on by default. Having it on should […] [ssrs display bullet in report]( - In an ssrs 2012 report, I need to place bullet dot marks on several lines. The dot looks like the following: • . Thus can you tell me on to setup this representation in the SSRS report? There is probably some kind of a character representation that I need to use. [Alias server names]( - Hello. This should be simple, maybe. I have to work with over a dozen servers all named such as VSKDFJLMM99003. I don’t want to (well, I can’t) rename the servers. But it would be nice that when looking at Object Explorer, they were labeled as something more readable, like “Mortgage_dev” or something like that. Is […] [is_rpc_out_enabled]( - Can someone tell me if this setting should be set to true or false? I have a list of linked servers and I have noticed that for some, it is set to true and for some, it is set to false. [web.config security best practice]( - So a dev has created a web app connecting into one of my dbs with a connectionString hardcoded into the web.config. We’re using a sql login. He has left. I want to harden it if possible. Can I create a dsn & reference this instead? Or is there something else I could do to secure […] [Text file import]( - Is there a script that scan take text that is not column delimited and transfer each line into a separate record and each line where it is separated by a number of spaces inserts into a separate column. For example the format of the txt file is as below but there are hundreds of lines. […] [limitations for RDS]( - Hello everyone What limitations can I have if I migrate to RDS SQL Server? thank you for your feedback [FACT TABLE with multiple text columns]( - The fact table as of now 100 + string fields. These are mostly fields like reference numbers. For example , in case of a shipment , the fields product and order reference numbers, BL numbers and references , vessel , voyage, seal numbers etc. These would repeat for a few shipments but majorly do not […]   [RSS Feed]([Twitter]( 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. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -