Long Title! Let me explain. This post will help you solve following problem if you run into it:
1) You are using SSMS Import data wizard to load data from a comma (or tab) separated value (CSV/TSV) file into SQL Server Table & you find that your source data values has double quotes and so you want to strip them before loading to destination table.
2) You are using SSIS to load data from a CSV/TSV file into SQL Server Table & you want to strip the double quotes in source table fields before you load the data to destination table.
1. After you’ve configured the Flat File connection. you’ll reach to a point where you’ll see “Flat File Connection Manager” in SSIS. Or in the SQL Server Import & Export data wizard, you’ll see a dialog box to configure flat file connections.
2. In the Text Qualifier, enter “
3. Make sure to Preview the data to verify that the double quotes around data fields have been trimmed.
4. That’s it! You’ve successfully configured the flat file connection manager to strip double quotes.
Environment: SQL Server 2012 reporting services 2012 w/ SharePoint 2010 Enterprise.
For SSRS reports hosted on SharePoint, the Data Alerts & Subscribe are grayed out or disabled.
What do you have to do?
You need to upload a SSRS and for the data sources, you’ll have to store the credentials or no credentials.
It’s not ideal for user-level security (e.g. via Windows Authentication) setup on the data source side but the requirement of data alerts and email subscription dictate that you need to stored the credentials or not have credential requirements.
If the New Data Alert option is grayed, the report data source is configured to use integrated security credentials or prompt for credentials. To make the New Data Alert option available, you must update the data source to use stored credentials or no credentials. – http://technet.microsoft.com/en-us/library/gg492251%28v=sql.110%29.aspx
what we ended up doing at a client’s was to create a SharePoint library for “report subscriptions” which is hidden from end-users. We added a service account to the data source & we store the credentials of the service in the report used for report subscription. And IT “controls” who receives the email. So after a user submits a request to get emails, IT goes in the security database & see’s if a user is fit to receive the email or not. So not all users may get approval to receive the email. That was a solution that we had to take to stay compliant.
Why do you want to do that?
One of the common tactic that you can consider to drive adoption of a Business Intelligence system is to integrate/embed the BI reports to the APPS/SITE that the users are already using. Don’t make your users come to you, go to them! As a part of that, I figured out a way to integrate/embed Power View in a Site that was used by existing user base.
You can integrate/embed Power View reports in SharePoint web Parts. Here’s How:
Image Credit & For step by step tutorial, please refer to: Integrate Power View with SharePoint using web parts
Environment: SQL Server 2012 in SharePoint Integrated mode w/ Power View Activated for the site. Also, SharePoint Enterprise 2010.
Topic: Integrating SSRS with SharePoint
Speaker: Kevin Goff
Have you ever seen a SharePoint site that integrates reports from SSRS, and wonder how all the pieces fit? If so, this session is for you. I’ll cover the necessary integration/configuration steps for integrating SSRS 2008R2/SSRS 2012 with SharePoint 2010/2013, as well as deploying reports to a SharePoint location. Because different versions of SSRS integrate differently with the available versions of SharePoint, I’ll provide a feature matrix for specific version integrations. I’ll also show how to integrate SSRS reports into SharePoint/PerformancePoint Services dashboard pages, as well as how to schedule reports for delivery to SharePoint document libraries. You’ll also see the improvements from Microsoft that make reporting against SharePoint lists much easier than before. At the end, I’ll show 2 new features in SSRS: Data Alerts in SharePoint, along with the new SSRS data visualization tool, Power View
I hope to see you there!
Paras | VP of Marketing | PASS BA VC
Take difference between two data values.
Let’s start writing some DAX!
Ok, seems simple, right? Try creating a measure DateDifference:=[EndDate]-[StartDate]
Did that work? NO? Does it return date?
Don’t worry, Here’s the solution. Try creating following DAX Measure:
That’s about it for this post. Here are some related Posts:
Calculate the difference between two dates in DAX
Q: How can I calculate difference between two dates in DAX (seconds, minutes, hours, days and months)
NETWORKDAYS() Equivalent in PowerPivot?
DateTime columns can be tricky for analysis purposes. They don’t work well with Pivot Tables because of the time part, each value seems unique to the Pivot Table & it also creates problems while creating relationships with Date Dimensions. And so, It’s a common need to convert them to just Date before analyzing data & also a common need to create a relationship between the Date (and not DateTime) with Date Dimension Table.
So if it’s possible, I try to do the data type conversion in the source system query. If your source system is SQL Server, you could use this piece of code:
select [your-fields],cast([DateTime_Col] as date) as Date_Col from TableName
Doing the data type conversion upfront in the source system query is a good thing to do. And I hope this is helpful.
Strange date relationships with #PowerPivot
Date and Date/Time – Sneaky Data Types!
As a part of developing ETL packages, sometimes, I’ve to write T-SQL queries to pull data from SQL server source systems. But before I start doing that, it’s always good to know the version/edition of the source system. Why? because it can determine whether a TSQL operators are available for me to use or not. Case in point, I had a requirements where I could have written a query that uses Pivot & UnPivot operators. So I write a query & it doesn’t work! I spent about 5 minutes trying to debug the code. The code seems OK to me. So I thought of checking the “version”. And there you go, client’s source system was running SQL Server 2000. So that meant, I couldn’t use the Pivot & UnPivot operators.
This was my quick note on how select @@version helps me while I’m TSQL’ing. Next time, I’ll probably check this first, before writing the code. That could save me few minutes :)
DAX (Microsoft’ Data Analysis Expressions Language) does not have a Substring function but I needed something like that for the following problem:
I had domain/username as input and I needed to extract just the username part of the string.
Input format: domain/username
output format needed: username
Input column name: UserID
so here’s the DAX formula I used: RIGHT([UserID],LEN([UserID])-SEARCH(“\”,[UserID]))
Note: The Formula is shown for demo purpose only, It may not work directly before making appropriate changes to the formula like making sure the column name is right & If the double quotes show problems, try deleting it & typing them back again.
Note that I combined some of the available DAX text functions to achieve what I was looking for. There might be other way to do this and I would be happy to learn about it too. Meanwhile, I hope if you reading this, this might give a good starting point while researching your DAX problem.
You can put the fields as filters in two different areas:
An end-user asked me the difference between the them today:
It’s helpful to have more than two chart/table in a view (think of view as one excel single sheet) to understand the difference. So let’s say you have two charts in a single VIEW:
How do you filter data in both charts?
By putting the field in the VIEW area.
They can be called View-Level Filters
How do you filter data in just one of the chart/table?
By putting the field in the Table/Chart area.
They can be called Visualization-Level filters.
Filtering, Highlighting, and Slicers in Power View
WordPress recently did a good series on how to analyze the data that’s available to you via WordPress Blog Stats tool. This series is great if you’ve a WordPress.com blog PLUS it’s a good read for any one in the data analytics role to learn how to write-up content like this.
1. Stats Wrangling I: Digging into Your Data
2. Stats Wrangling II: Days, Weeks, and Months
3. Stats Wrangling III: Top Posts and Pages
4. Stats Wrangling IV: Referrers and Clicks
5. Stats Wrangling V: The Words that Bring You Traffic
Along with WordPress Stats, I also use data from the Google Webmaster Tools. It’s a great way to see Keywords, Top posts & pages from a search engine point of view. It’s always good to have a healthy number of people searching for your content on search engines like Google.
I hope you take a look at how Data Analytics can help your Blog Grow. The series that WordPress ran focused on their platform but if you run your blog on other platform, this should give you a good sense of how to analyze the blog statistics.