The lack of user-defined tables in serverless SQL pool is probably one of the biggest gaps one needs to overcome as developer in Azure Synapse. On the other side, given that views are "virtual tables", views can be misused to store small chunks of data that don't change that often.
In the process there are two activities involved - preparing the data, respectively creating the view. Some developers might prefer preparing the data in Excel though, when possible, I prepare the data directly from the source system. For example, the first four columns from the below query can be used for the first approach, while the last column prepares the data as needed by a VALUES.
-- Product subcategories denormalized SELECT PCT.ProductSubcategoryID , PCT.ProductCategoryID , PSC.Name Category , PCT.Name Subcategory , CONCAT(', (''', PCT.ProductSubcategoryID, ''', ''', PCT.ProductCategoryID, ''', ''', PSC.Name, ''', ''', PCT.Name, ''')') [Values] FROM Production.ProductSubcategory PCT JOIN Production.ProductCategory PSC ON PCT.ProductCategoryID = PSC.ProductCategoryID
Independently of the method used for data preparation, the view can be built as follows:
-- creating the view CREATE VIEW dbo.ProductCategories AS -- Product categories SELECT Cast(DAT.ProductSubcategoryID as int) ProductSubcategoryID , Cast(DAT.ProductCategoryID as int) ProductCategoryID , Cast(DAT.Category as nvarchar(255)) Category , Cast(DAT.Subcategory as nvarchar(255)) Subcategory , Dense_Rank() OVER (PARTITION BY DAT.Category, DAT.Subcategory
ORDER BY DAT.ProductSubcategoryID, DAT.ProductCategoryID) Ranking FROM (-- prepared data VALUES ('1', '1', 'Bikes', 'Mountain Bikes') , ('2', '1', 'Bikes', 'Road Bikes') , ('3', '1', 'Bikes', 'Touring Bikes') , ('4', '2', 'Components', 'Handlebars') , ('5', '2', 'Components', 'Bottom Brackets') , ('6', '2', 'Components', 'Brakes')
) DAT(ProductSubcategoryID, ProductCategoryID, Category, Subcategory) -- testing the view SELECT * FROM dbo.ProductCategories
No comments:
Post a Comment