Posts

ISIN Validation SQL Function

CREATE FUNCTION dbo.IsValidISIN(@ISIN NVARCHAR(12)) RETURNS INT AS BEGIN DECLARE @Check INT --Check digit not available IF RIGHT(@ISIN,1) NOT BETWEEN '0' AND '9' BEGIN  SET @Check=-1  RETURN @Check END IF PATINDEX('%[^0-Z]%',@ISIN)>0 BEGIN  SET @Check=-3  RETURN @Check END DECLARE @Data NVARCHAR(30)=LEFT(@ISIN,11) DECLARE @MultOdd INT= 1, @MultEve INT= 1 DECLARE @Sum INT=0,@StrOdd NVARCHAR(60)='',@StrEve NVARCHAR(60)='' DECLARE @Letter INT,@Text VARCHAR(1) SET @Letter =PATINDEX('%[A-Z]%',@Data) SET @Text=SUBSTRING(@Data,@Letter,1) BEGIN  WHILE @Letter>0 BEGIN   SET @Data=STUFF(@Data,@Letter,1,ASCII(UPPER(@Text))-55)   SET @Letter =PATINDEX('%[A-Z]%',@Data)   SET @Text=SUBSTRING(@Data,@Letter,1)  END END --Get Length Multiplier IF LEN(@Data)%2=0 BEGIN  SET @MultEve = 2; END ELSE BEGIN SET @MultOdd = 2; SET @Letter = 1; END WHILE @Letter<=LEN(@Data) BEGIN  IF @Letter%2=1 BEGIN   SET @StrOdd=@StrOdd+CONVERT(VARCHAR(60),CO...

xml example

library(httr) # Define the URL of the API endpoint url <- "https://your.api.endpoint" # Define the data to be sent in the POST request (replace with your actual data) data <- list( key1 = "value1", key2 = "value2" ) # Define the token (replace with your actual token) token <- "your_access_token" # Create the POST request with headers response <- POST( url, body = data, add_headers("Authorization" = paste("Bearer", token)), encode = "json" # Specify the encoding, e.g., "json" ) # Check the response status_code <- status_code(response) content <- content(response) cat("Status Code:", status_code, "\n") cat("Response Content:", content, "\n")

XML row count using python

 # import required module import os import xml.etree.ElementTree as ET # assign directory directory = 'D:\\XML\\Data' # iterate over files in # that directory for filename in os.listdir(directory):     f = os.path.join(directory, filename)     # checking if it is a file     if os.path.isfile(f):             tree = ET.parse(f)             root = tree.getroot()             num_rows = len(root.findall('.//Product'))             print(f, num_rows)

Migrating SQL Server Reporting Services (SSRS) from a busy server to a dedicated server can provide several benefits

 Migrating SQL Server Reporting Services (SSRS) from a busy server to a dedicated server can provide several benefits, including: Improved performance: When SSRS is running on a busy server that is also hosting other applications, it can cause performance issues for SSRS. Moving SSRS to a dedicated server can improve the performance of reports and reduce the time it takes to generate and deliver them. Scalability: A dedicated SSRS server can be more easily scaled than a busy server that is hosting multiple applications. As your reporting needs grow, you can add more resources to the dedicated server to ensure that it can handle the increased load. Better resource management: By moving SSRS to a dedicated server, you can better manage the resources allocated to reporting. You can configure the server to optimize its performance for SSRS, which can result in more efficient use of resources and faster report generation times. Increased security: By separating SSRS from other applicati...

Python code to unprotect the excel sheet with a valid password

import msoffcrypto import pathlib # pip install msoffcrypto-tool' url = pathlib.Path(r'D:\Source') excel_files = list(url.glob('*.xlsx')) def unlock(filename, passwd, output_folder):     temp = open(filename, 'rb')     excel = msoffcrypto.OfficeFile(temp)     excel.load_key(passwd)     out_path = pathlib.Path(output_folder)     if not out_path.exists():         out_path.mkdir()     with open(str(out_path/filename.name), 'wb') as f:         excel.decrypt(f)     temp.close() for i in excel_files:     unlock(i, 'Password', r'D:\Destination') ------------------------------ # Replace "C:\Your\Directory\Path" with the actual path to the directory you want to search in. $directoryPath = "D:\Testing" # Replace "2023-06-30" with the date you want to check in "YYYY-MM-DD" format. $targetDate = "2023-06-30" # Get all the *.txt files in the specified directory that match the ...

SQL Server 2012: Multidimensional vs tabular

Image
which model should you use, Multidimensional vs Tabular? Well, there is no clear-cut answer, but there are some factors that can make you choose one over the other: If you want to use DAX, you have to use Tabular If you want to use Power View, you have to use Tabular If your dataset is extremely large, go with Multidimensional If you need writeback support, you have to use Multidimensional If you need access to many different external data sources, choose Tabular If you need complex calculations, scoping, and named sets, choose Multidimensional If you need extreme speed and consistently fast query time, choose Tabular If you need Many-to-Many relationships, choose Multidimensional (can be done in Tabular but difficult) If your solution requires complex modeling, choose Multidimensional If you need any of the following features, you must use Multidimensional: Actions, Custom Assemblies, Custom Rollups, Custom Drillthrough Actions (but BIDS Helper adds support for action...

DAX Tabular Model

Why Tabular? SQL Server Analysis Services is a solid and mature platform that now serves as the foundation for two different implementations.  Multidimensional models are especially suited for large volumes of dimensionally-structured data that have additive measure values that sum-up along related dimensional attributes & hierarchies.   By design, tabular architecture is more flexible than multidimensional in a number of scenarios.  Tabular also works well with dimensional data structures but also works well in cases where the structure of the data doesn’t resemble a traditional star or snowflake of fact and dimension tables.  When I started using PowerPivot and tabular SSAS projects, I insisted on transforming data into star schemas like I’ve always done before building a cube.  In many cases, I still do because it’s easier to design a predictable model that performs well and is easy for users to navigate.  A dimensional model has order and...