Presented at #sqlpass summit 2015.
You have your SQL Server Reporting Services environment in native mode — and you want to modify the data source of a report there.
- Navigate to Report Manager.
- Navigate to the Report that you want to Manage and run it
- After the report renders, you will have a breadcrumb navigation on the top right
- Click on the Last Part of the Breadcrumb Navigation
- It should open up the “properties” section of this report
- On the properties section, you should be able to manage the data source
- Make the changes that you wanted to the data source settings of this SSRS report — and don’t forget to click “apply”
Author: Paras Doshi
I was talking with a database administrator about different categories that SQL Commands fall into — and I thought it would be great to document here. So here you go:
|DML||Data Manipulation Language: SQL Statements that affect records in a table.||SELECT, INSERT, UPDATE, DELETE|
|DDL||Data Definition Language: SQL Statements that create/alter a table structure||CREATE, ALTER, DROP|
|DCL||Data Control Language: SQL Statements that control the level of access that users have on database objects||GRANT, REVOKE|
|TCL||Transaction Control Language: SQL Statements that help you maintain the integrity of data by allowing control over transactions||COMMIT, ROLLBACK|
BONUS (Advance) QUESTION:
Is Truncate SQL command a DDL or DML? Please use comment section!
Author: Paras Doshi
Are you trying to import an Excel file into SQL Server using SQL Server Integration services…And ran into error that has words like “Non unicode” and “unicode”? Then this blog is for you.
Why does this error occur?
Well it turns out that things like SQL Server and Excel have encoding standards that they follow which provides them a way to process, exchange & store data. BUT turns out that SQL Server and Excel use different standards.
So, the solution is simple right? Import the data from Excel into non-Unicode format because that’s what you need for SQL Server.
So how do you that? Between your Source and Destination tasks, include a task called “Data conversion” and do the following for all columns that have text:
And in the destination task, you’ll have to make sure that the mapping section using the new output aliases that you defined in the “data conversion” step.
In this post, we learned about how to solve a common error that pops up when you try to import excel file to sql server using SSIS. Hope that helps.
Author: Paras Doshi
I spend a lot of time writing SQL code — and as a reader of this blog, You might be in the same boat. So any productivity gains that we could get here could go a long way. On that note, here’s a quick productivity tip: Learn to comment/uncomment multiple lines of SQL code using keyboard shortcut.
If you are using SQL Server Management Studio, it’s “CTRL-K followed by CTRL+C” for commenting AND “CTRL+K followed by CTRL+U” for uncommenting.
If you are using some other Data Management Software tool, I am sure you can find it using their HELP section or googling around.
Either ways, these shortcuts go a long way in making you more productive! What is your favorite productivity tip?
It’s been amazing to see the growth of Business Analytics community over the past couple of years as one of the chapter leaders on the PASS Business Analytics Virtual chapter…Here’s a data viz that I put together to analyze effectiveness of our marketing campaigns:
Here’s the chart:
May’14 = 100 attendees. Jun’15 = 223 attendees. % Diff = 123%
With this growth rate, we should have ~500 attendees in our future virtual chapter meeting in Jun 2016. Can’t wait! :)
A lot of work by Dan English (current president) and Melissa Demcsak (Immediate past president) went into growing this chapter!
Looking at user’s first/last transaction details is a common requirement. So given that you have a “user_transaction_details” table which looks something like:
Transaction_id | user_id | transaction_date | purchase amount
How would get first/last transaction details for each user account?
Here’s a design pattern:
select AllTransactions.user_id,AllTransactions.purchase_amt from user_transaction_details AllTransactions inner join ( select user_id, min(transaction_timestamp) as first_transaction_timestamp from user_transaction_details group by user_id ) FirstTransaction on AllTransactions.user_id = FirstTransaction.user_id and AllTransactions.transaction_timestamp = FirstTransaction.first_transaction_timestamp
To get the last transaction details for each user account, replace min function with max and that should be it.
Note: Depending on your data model and how you used it in the join, it might be that there would be multiple rows marked as “first/last” transaction and so would want to spend some time figuring out how to deal with these multiple row situation especially if you’re planning to further join this data.
In this post, I shared a design pattern to write a SQL query to get first/last transaction details for each user account type.
Question for you:
How would you solve this problem? Share you query in the comments section!
Attention to detail is a key in creating SSRS reports/dashboards that look like a work of a professional; To that end, here’s a tip: How to capitalize the first letter in your string? In other words, how to Camel Case the Text?
Here’s the function that you can use in your SSRS Expressions:
|hello world||StrConv(“hello world”,3)||Hello World|
I hope that helps!
In this post we will see how to address the SSAS warning message: “The name specified for the attribute relationship differs from the name of the related attribute”, it’s not a critical waning but it’s always good to make sure that these warnings are addressed before going to production.
Usually this happens because attribute names were renamed after the relationships between attributes had already been defined.
To fix the warning messages:
1. Go to Attribute Relationships section for the dimension.
2. In the lower right corner, you should find list of relationships.The ones that cause the warning would have a blue squiggly line with a warning symbol on the arrow (example shown below):