Sunday, July 7, 2013
From msdn online, here is a summary of the difference:
"An xVelocity memory optimized columnstore index, groups and stores data for each column and then joins all the columns to complete the whole index. This differs from traditional indexes which group and store data for each row and then join all the rows to complete the whole index."
The biggest difference is that data is grouped and stored one column at a time. The benefits are: because only the columns needed are read, this results in less disk reads, better compression, improved buffer pool usage (reducing I/O), and utilizes batch processing which reduces CPU.
Wah? Yes! Wah? I'll say it again, YES! And yes, it works with table partitions. Of course, there are logical limitations to it, like the most important being the table must be Read Only. But, for normal data warehouse architecture, data is not transactional and is loaded at certain times, most normally once or maybe a few times a day. So dropping and recreating indexes are something most data warehouse engineers are familiar with, in detail. Or you can always implement partitions and then switch, as well as other options to get around the read only requirement. There are other limitations, which I encourage you to explore, as well as some gotchas.
A fantastic, and thorough walk through of this new, exciting, feature in SQL Server 2012 can be found here: What's new in SQL Server 2012: Using the new Columnstore Index, it's a YouTube video by Kevin S. Goff.
My hat's off to the SQL Server development team, warms my heart that you addressed the growing needs of us data warehouse minions.
Friday, June 21, 2013
Some sites may want more collaboration, the ability to edit discussion topics, etc. I would highly recommend turning on version control for the list you modify in order to see who changed what. Here are the steps:
You need to be in the Site Owner group to make these changes. If you don't have the appropriate permissions, contact your SharePoint administrator to assist.
Go to Site Contents, click on the discussion list name and in the ribbon click on List, then List Settings.
Once in List Settings, click on Versioning Settings and make sure Create a version each time you edit is set to Yes. I typically do not require content approval for submitted items, but this is an option. It would not allow anything to show until one member of the Approval Members group approves the content.
Once you click "OK" on the Versioning Settings screen, go into Advanced Settings.
Under the Create and Edit access, select the "Create and edit all items" instead of the "...created by the user" like so, then be sure to click the OK button.
Next, we're going to alter the Site Members contribute rights. Click on the Gear in the upper right hand corner and select Site Settings, then under Users and Permissions, select Site permissions. Check the box next to the [Site Name] Members name, and the "Edit User Permissions" option on the ribbon will become available.
Click the Edit User Permissions, the default rights should be set to only Contribute. Check the boxes next to Edit and Approve like so:
Then click the "OK" button. Now have a site member test the ability to "Edit" a discussion but finding a discussion topic, clicking the ellipses and seeing the "Edit" ability like so:
Hope this helps.
Tuesday, June 18, 2013
When creating or editing a discussion, the text field for the body of the discussion is way too narrow. Can it be changed?
There are a few ways, the easiest is to implement JQuery in a web part on the page, that way no-one is mucking with the templates in SharePoint - which can be overwritten in patches, etc.
This following way would need to be done at each site or sub-site as the editform.aspx is unique (copied from the site collection level once a site is created). Which means implementing this at one site will not affect this form on any other site.
Note: You need design rights or site ownership rights in order to perform this modification.
Go to the site you would like to implement this on.
Go to a discussion, click the Ellipses (...) and then the Edit choice and you should see a layout similar to this where the "Body" textarea is fairly narrow.
You'll want to edit the page this form displays in, which is why I've highlighted the gear in the upper right hand corner, click on it and then select "Edit Page":
After you click "Add a Web Part", chose Media and Content, then Content Editor and click "Add":
Now you will see your page again with your newly added Content Editor web part. Click the "Click here to add new content":
It will then place your cursor in the Content Editor, but what we want to do is edit the HTML, so select the Edit Source button as shown here:
In the HTML Source editing window, type the following code:
Your window should now look like this:
Click the "OK" button at the bottom of the HTML Source window. Then you are back on the edit page, click the "Page" on the ribbon and select "Stop Editing" as shown here:
It will redirect you to the discussion list, so go back into a discussion, click the Ellipses and Edit, and now your text areas should be wider like this:
Friday, August 17, 2012
So I started my search to determine if a user was on our network from their desk or via VPN. My network engineer pointed me to the interface showing the logs. I saw how simple the output was and believed it was just reading from a text file. Sure 'nuf, found the text file locally on the domain controllers. There are probably multiple ways to determine how someone is connected, but I couldn't come up with any off the top of my head after a brief pow-wow with my network engineer, so this is the direction I went.
I found the domain controllers that held the logs (.csv files) for passed authentications which was located in ProgramFiles\CiscoSecure ACS v4.0\Logs\Passed Authentications and the files were named: Passed Authentications Active.csv. Ahhh, data, data, data...
When a user logs into the simple time clock application, I check these log files for the user name and text "Remote Access (VPN)" to determine if it's been more than a certain amount of time, say an hour, to attempt to verify this web app is not being accessed by someone connected via VPN.
An over simplified schema of how I solved this problem:
If you need more details, let me know.
Tuesday, July 24, 2012
I saved a few .rdl files to the new folder, created a new and updated .rds (shared data source) and attempted to deploy to the new server, to a subfolder from the main reportserver folder.
I was repeatedly prompted for my login. After some futzing around, I attempted to strip all the sub folder items out of the project properties and I found I could deploy to the main reportserver folder so I thought I'd take a screen shot of the correct configuration for future reference.
What I had been doing is adding after /reportserver/[name of sub folder], where in reality you need to add that to the "TargetReportFolder" and also if you want your data source to be under that subfolder, update the "TargetDataSourceFolder" to the subfolder/data sources url as shown here.
Hope it helps.
Thursday, March 15, 2012
This is one of those that more applies to SQL Server 2008 and beyond.
Did you know that tempdb not only stores temporary tables, but SQL Server also utilizes it for grouping and sorting operations, cursors, the version store supporting snapshot isolation level, and overflow for table variables?
Hopefully knowing these other objects that can be highly utilized even if you do not believe your databases utilize a large amount of temporary objects will arm you with ammunition to vote for more and different physical disks for tempdb.
Disk cost cannot dwarf the speed benefit you will have, especially in data warehouse situations where some BI solutions create very large temp tables to join on before returning results.
Thursday, November 10, 2011
Thursday, July 7, 2011
Change is good, even if it appears big and scary at first…
Ever find a MUCH easier way of doing things and just fell in LOVE with it? It was that way for me once I discovered table valued functions in SQL Server. From that moment on I saw everything as a function “hill” to climb. My happiest moment: turning a query which took over 90 seconds down to 3 seconds because of this discovery and a subsequent re-architecture of all database calls.
Fast forward to… now… the OUTPUT clause. I know, I know, been around since SQL 2005 but until you wrap your head around it, create a new database or have time to re-architect something old, you can’t really get the implications until you incorporate it in a new database design.
One call to a database to update or delete can not only save your data, but OUTPUT the data you want to save in another table for, oh I don’t know, change management, historical, CYA implications.
DELETE FROM [table] OUTPUT deleted.* INTO [tablearchive] FROM [table1] t1 JOIN [table] ON t1.ID=[table].table1_ID WHERE ID = 123
Double duty – deleting (or updating or inserting for that matter) and saving whatever details from that statement into another table. Saving yourself either an additional call from the front end, or at the very least, an additional SQL statement.
Just keeping it simple.
Monday, June 13, 2011
Honestly, I use this one several times a month, so putting it here for safekeeping.
Tuesday, March 8, 2011
public void DoesNotLeakConnections()
Using (SqlConnection sqlconnection1 = new SqlConnection("Server=.\\SQLEXPRESS ;Integrated security=sspi;connection timeout=5"))
SqlCommand sqlcommand1 = s qlconnection1.CreateCommand();
sqlcommand1.CommandText = "raiserror ('This is a fake exception', 17,1)";
sqlcommand1.ExecuteNonQuery(); //this throws a SqlException every time it is called. sql connection1.Close(); //Still never gets called. }// Here sqlconnection1.Dispose is _guaranteed_}
Tuesday, January 4, 2011
If so, try searching the entire solution for the ID – you should find the original object you copied and the search will also give you a hint of what object it was copied to that you need to reproduce.
In the package, do a Edit -> Find and Replace -> Entire Solution and paste in the annoying and mysterious ID in the error message received from the SQL Server Agent job that fails. This search should reveal at least two, most likely four lines where this ID shows up. Select which object is in the .dtsx package which is failing. If you scroll to the right in the Find Results screen, you should find the user friendly name of the object. It will be something like:
</DTS:Property DTS:Name=”ObjectName”>Some Object Name</DTS:Property>
Now you have the user friendly name of the object. You should RE-create (from scratch – do not copy/paste) the connection. Use this new connection in the task(s) necessary, then delete the old connection, rebuild and deploy. Test again, if possible, from SQL Server Agent.
From my research it seems that Visual Studio has issues sometimes with copying connections from one package to another. Sometimes it keeps the old ID in the manifest or logging, and sometimes it even erases the ID and coughs up blood.
Hopefully, helpfully yours… Lara
Tuesday, November 30, 2010
This information would have been helpful, ohhhhh, a few months back, after countless (and random, of course) timeout occurrences and lots of hair loss and shoulder shrugging.
Background: I built a .net console application that downloads our HR data from a “major” HR provider. This project consisted of security certificates (purchasing and exchanging) on all provider server as well as client server; logins and passwords and research, research, research. The company provided sample code, which basically showed how to query one module at a time and one module at a time.
But my task was to download all the data for all employees every day, including data for terminated employees. We have over 600 active employees, which employee has, obviously, multiple rows in most, and sometimes more than one, in each module of data. I call them modules, for lack of a better term. This equates to downloading approximately 70,000 records once a day. The connection and download takes approximately 9 minutes. Of that 9 minutes I’d say a few are spent waiting for a response, I’d guess that the actual workings take about 5 minutes, not bad I think but I don’t have much to compare it to at this point.
The provider does not provide a “changed” mechanism (or at least they did not tell me they have that mechanism – although many of the hurdles I came across on this journey were questions that when asked multiple times were simple answers – maybe the question just had to be asked of the right person?). Anyway, I stray… One of the first errors I would receive, randomly, would be a timeout attempting to download data from a module. And one particular module would always timeout so that I had to break up the requests by choosing an additional filter.
So, seemed to be pretty stable with a timeout occurring once a week or two, I could handle that, especially since when I asked them the question and sent them the error messages, I was given the response “it’s not on our side”… Time passed, and timeouts increased to a point where I couldn’t get that one module, previously broken down into four filtered requests, to even budge.
I sought empathy from our network engineer to monitor the traffic back and forth and also re-wrote most of the application to save the SQL insert commands to a lengthy string and execute after each module completed, so I wasn’t shooting off single SQL insert statements continually. Still nothing seemed to help. I narrowed it down, with debug printable statements, and could show that there was over a 1.5 minute delay from when a request was sent and a response on that one pesky module. Everything I’d researched stated the timeout value was set inside IIS on the provider server.
After compiling my error list and debug statements into a very convincing e-mail to our HR application provider, I finally received a response from them telling me to up my timeout setting to 5 minutes… Huh?
And this is what you get with someone who learns C# from a book and sample code, over having someone who is an expert mentor you…
Simple addition of this line in each module call to download data:
[ServiceName] proxy = new [ServiceName]();
proxy.Timeout = 300000;
Yep, just that simple. Months of frustration being fixed with 23 characters. Sometimes you feel like an idiot at the end of the day. Live and learn.
Thursday, November 4, 2010
Monday, October 18, 2010
Wednesday, September 22, 2010
There is a great video tutorial and link to the free download here: brentozar, and even a way to save the results over time to a table.
Monday, July 5, 2010
After creating a database, change the default filegroup from primary to anything else. If you only have one filegroup, create a secondary one. Then alter that filegroup to become the default.
My normal procedure is to create the database, specifying a secondary file group in the Filegroups page in the New Database screen in SSMS. After that, I create a file in the new Filegroup and make that new Filegroup the default.
--alter database, add file to Filegroup ALTER DATABASE [DBNAME]
ADD FILE (NAME=[NewFileName], FILENAME='D:\Data\[NewFileName].ndf')
TO FILEGROUP [FileGroupName] --(the name used when creating the Filegroup
ALTER DATABASE [DBNAME]
MODIFY FILEGROUP [FileGroupName] DEFAULT
Reasoning: When the database is created the primary filegroup is by default marked as the default filegroup in which all new objects are stored. The primary filegroup also happens to be where all the system objects are held.
So once you create a database and make any other filegroup the primary, all the system objects (system tables for the database) are in their own filegroup with I/O isolation. Seeing as these system objects do not change as often as all the other database objects, this minimizes write activity to the primary data file and will assist with reducing corruption due to hardware failures.
Another benefit this will give you is to be able to do maintenance at the filegroup level. More to come about the benefits of filegroups and files in a future post.
Saturday, June 12, 2010
(Read Part 1, Part 2 and Part 3)
I rarely give up, rarely say things like "I can't"... so with persistence comes big pay off.
- I've created a simple front end giving the finance users the ability to "push" their own button.
- I make sure the ETL isn't already running
- I save all button pushes to a history table for displaying in the web app
- I display some feedback to the user (or any visitor for that matter), letting them know what major milestone the application is working on
- I've combined .NET, T-SQL, MicroStrategy .dll's, a SQL Server Job, and a Windows Scheduled Task, and a SQL stored proc to send the final e-mail, notifying all Finance Managers the process is complete
It brings a smile to my face when I wake up in the morning to several e-mails notifying me that someone overseas has pushed their own button without waking me.
Sunday, June 6, 2010
(Read Part 1, or Part 2)
You'd need the MicroStrategy SDK license if you want to be compliant with the usage of this code. FYI - we were on MSTR 9.0.209.
- I added a operating system command (CmdExec) step to the end of my SQL Job, called it Run Batch File to Refresh MSTR.
- I created the code in C#, just a simple console application, called MSTR_Refresh.exe and deployed it to the same server that held our MicroStrategy Intelligence Server service.
- I created a Windows Scheduled Task on the MicroStrategy Intelligence Server which runs the code, located locally, along with the necessary MicroStrategy dll: Interop.DSSCOMMMasterLib.dll
- The program consists of defining the connections, and calls the ExpireAllCaches(0) (clearing the report cache)
- And finally it updates all cubes.
- For this particular example, I only needed this on one project, but you could loop through multiple if needed.
Clear All MicroStrategy Caches:
Update MicroStrategy Intelligence Cubes:
Saturday, June 5, 2010
The current architecture of the ETL (not written by myself) is a series of MS SQL job steps, mostly consisting of calls to stored procedures. To determine the time it took for the steps, I used SQL Profiler. I determined where would be the best place inside the 50+ steps to update the ETL_StepHistory table, and updated the corresponding stored procedure to write to the table.
I also created a few stored procedures:
RecordETLSteps and takes a parameter @comment (varchar(255))
This proc is called at the six random spots inside the ETL steps, calls the stored procedure and passes whatever description I want to display, an example: pulling data to fill lookup tables, or loading forecast data.
TestJobStatus which takes a parameter of @jobname (varchar(255)) - so I could potentially reuse this to check any other SQL Job. I had nothing in mind at the moment, but always a good idea to make even simple procedures scalable in my mind.
In my web app, I do a dreaded Thread.Sleep(2000) and then check the step history table, displaying the details in an asp:Repeater in an asp:UpdatePanel. Then checks the job status utilizing the above proc, sending in the name of the SQL Job. If the return value states the job has completed, I enable the "Run ETL" button once again for all users, and save the status: "Job completed, sending e-mail" into the steps data table and page display. The e-mail is sent from within SQL to an AD group of all users who would need to know the ETL just finished.
Stay tuned for Part 3 where I'll discuss the MicroStrategy piece, including code snippets.
Tuesday, June 1, 2010
When I first arrived at my current job, in addition to the nightly ETL run, whenever a finance manager uploaded his or her data through a custom application, they would send an email to me to "push the button". Since the company is world wide, some of these requests would be after my normal working hours. Their ETL process consisted of kicking off a MS SQL job, waiting until the job completed it's nearly 50 steps, then log into MicroStrategy desktop to clear the report cache and intelligent cubes. Once that process was completed, if I was still paying attention in the middle of coding, notice and send all the finance managers in the company an email stating "ETL has been run..."
Well after the fifth time of "pushing the button," I'd had enough.
Create an internal simple web application to give finance managers the ability to press their own button. This would kick off the ability to start the ETL, and then the MicroStrategy processes, and at the end would send the email to all concerned.
- I needed to make sure only one person could push the button and the rest would be locked out from pushing the button while it was already running.
- I wanted a way of keeping track of who pushed the button when (so a history).
- Web app needed to be locked down to have two levels of security.
- Admin for users to see history
- Regular finance users, and I didn't want to maintain the users but utilize AD groups.
- How to interface with MicroStrategy...
I piggy-backed on our existing MicroStrategy web server for the internal web server for the location of the front end, and had my system admin create an internal DNS for it for ease of accessibility. I also procured the MicroStrategy SDK license so I had access to their documentation and .dll's.
Building the web app:
Security: I locked down a subfolder (Admin) of the new site and used the
Two main pages to start for the general users, an ETL_Run page and ETL_History page. The history is a simple list view connected to a SQL connection which reads from a simple history table I created.
New Data Objects:
Two new tables: ETL_History and ETL_StepHistory. The ETL_History table has just an identity column, as well as the date and user. The ETL_StepHistory is updated at each major milestone in the ETL (to give some feedback to the user as to where in the process it is as it can take up to 30 minutes for the entire process to complete). This way the front end just pings this ETL_StepHistory table and displays the data. When a user kicks off a new ETL, this StepHistory table is truncated, and has a step identity column, description and date/time columns.
Tune into Push the button, part 2 coming soon.