Recently I ran accross the need to export a very large amount of data to Excel. This was not just a “dump” of one table, but required some relatively complicated filtering as well as tying multiple tables together based on inconsistent criteria. In a nutshell … it wasn’t going to be difficult, just BIG, and I’ve dealt with more onerous requirements (heck, even runninng this through SSRS it shouldn’t have been a problem).
That being said, I decided to make it easy for my NAV users to run this anytime they wanted on their own in the future. The data they wanted can and does change relatively frequently – specifically it gets added to. So, I created a process only report that exports to Excel using the built-in Dynamics NAV Excel Buffer table.
I ran sample reports. They tested great.
I ran sample reports with more data (30,000 rows in Excel). These also tested great.
With everything set and everyone signed off on a final format for the data, I went for a “full” export. And … crash.
Operating System Returned the Error (131). An attempt was made to move the file pointer before the beginning of the file.
Multiple attempts to watch the screen (I have a dialog progress window up) and tracking possible data issues and I had to resort to the best diagnostic tool I had. The search engine on the internet. I then found that any temporary table that exceeds 2 GB will crash NAV.
Ok, first off I have to admit it kinda made me a little taken aback that my Excel Buffer temporary table was getting to be 2 GB in size! Then I remembered I have 16 columns for every row of data and every column has a complete record saved in NAV. Doing a little calculating, I need a LOT more space! NAV was simply not going to be able to handle this as-is.
Without making a long post too boring, I am writing this with the intention of making a suggestion to everyone who is looking to do something similar to this. I ended up simply writing my contents directly into Excel every 10,000 records into the buffer table. This kept my temporary table file at 363 MB and let me write … well, a LOT of data to my excel file without worrying about the issue of a temporary table limitation.
The two functions I created (the first to be called every 10,000 rows written to the buffer, the second to be called at the end of the report) are:
CreateExcelWorkbook()
IF FirstCreate THEN BEGIN
TempExcelBuffer.CreateBook;
FirstCreate := FALSE;
END;
TempExcelBuffer.CreateSheet(‘Export’, ‘Export’, COMPANYNAME, USERID);
TempExcelBuffer.DELETEALL;
CreateFinalExcelWorkbook()
IF FirstCreate THEN
TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateSheet(‘Export’, ‘Export’, COMPANYNAME, USERID);
TempExcelBuffer.GiveUserControl;
What does your ERP system, your mobile phone and your tablet computer have in common? These days, likely not much. If you are trying to be on the cutting edge, you may have a browser based link into your ERP system that can be used on each of these systems. If you’re not one of the avante garde in this technical world then you’re in luck (yes, meaning you didn’t spend your money on something that will be obsolete soon).
To be clear, integration to web solutions for customers or business to business sites will continue as they have for the moment. There is not any trend or technology pushing the boundaries on these fronts beyond the need for a custom website with backend integration (even if that “custom” website is based on a template).
But for internal use, on-demand reporting, live updates and management or sales dashboards on any and every device you use in business … Get ready for a great change coming in way of technology from Microsoft. Yes, Microsoft. Not Apple or Google (these are great end user phones and tablets, but are not as business focused).
With Windows 8 and Windows Phone 7.5 (Mango), Microsoft has started to build a synchronous platform that integrates their tested and global programming platforms (Visual Studio) together on every device you might own. This is not in the distant future but is already starting now. Dynamics CRM implementations can take advantage of dashboards through Windows Phone devices. These dashboards will soon port over and be available on tablets running a FULL desktop software of Windows 8 with the same look, feel and ease of use as found on the phone. The blending of information and ease of use is blurring for businesses more and more.
If you need to know your quarterly, monthly or weekly running sales figures – simply look at your desktop tile for Dynamics NAV (this is already in process to being created by several studios). If you need to have your message system include Dynamics NAV or other Dynamics ERP alerts – such as vendor spending, PO approvals, document management, inventory alerts and many other features at your fingertips – it will be coming soon and will be seamless between your desktop, tablet and phone on how this information is gathered and displayed.
The future of ERP systems and efficiency has never looked brighter. Keep your eye on Windows 8 and the Windows Phone platforms – as a business you can’t afford otherwise.
Your salespeople still carry a personal day planner stuffed with notes and business cards. Your warehouse manager is so busy tracking and overseeing their inventory movement that they don’t have time to talk about improving efficiency. Your services department is full of qualified professionals who cannot seem to fill out their paperwork the same from week to week.
Everyone in your business has their habits and unique hurdles for your growth. How can you understand your sales pipeline without seeing a good metric of what sales are in each stage – or what marketing campaigns brought those sales in the door as a cost comparison? Warehouse efficiency would give control of your warehouse manager’s time back to them, but how do you give efficiency that is easy and usable at a glance? Your services department needs a streamlined data entry system easily available to your workers that they can use anywhere at any time – how will this possibly integrate back to real data and invoicing?
A good ERP system should be able to answer these questions. Here are some additional questions to ask yourself about the software that manages your business:
- Does my current solution help my sales team generate new business and find the most successful venues for future marketing campains?
- Can I as an owner look forward 6 months with reasonable accuracy to see where my business and future growth will likely be? 12 months?
- Is my warehouse saving me money on every shipment?
- How accurate and easily can I review my inventory to know my costs and forecast inventory pitfalls?
- Will my software integrate with outside data collection, allowing me to create streamlined processes for those users that don’t need the “full” software packages getting in the way of day to day operations?
While this is just a short list of many questions that can be asked, it is a good starting point to know your business. Are you ready to weather bad times? Are you ready to handle explosive growth? Do you know which is coming? If you have questions about the possibility of letting your users be the individuals who drive your business to success – while more tightly integrating those individuals to a complete picture you need to see – then perhaps it is time to have a talk with your ERP vendor to find out more. Don’t settle for second-best options when the best will save you time, money and possibly your future as a business.
Where are your most important documents or spreadsheets and can you find them quickly?
Finding a document is sometimes an easy enough task these days. Most users work with no more than 10-12 critical documents per week and the “Recent” list in many document tools more than covers the easy retrieval of these files.
However, ask these same users to find their files and allow others to view or edit these files and you end up with a copy of the file now on their hard drive, in email and on the network somewhere. If you’re lucky. If you’re not so lucky you end up with at least a half-dozen versions of the document running around and now finding the most reliable source file becomes a nightmare.
Enter the magical new catch-phrase that is all to common these days …
The Cloud.
It will solve all of your problems. You can share your documents with ease. Your editing and source control nightmare is now over. Data integrity and version control is built-in. Cloud redundancy means no backing up.
The point here is not that these idioms are false – but that they are not new. This technology has been reliably available for over a decade. The difficulty has not been in the technology but in the presentation and adoption of these systems.
Right now, however, the major players in the world of document software are vying for your attention. They want your money, and are willing to do whatever you want to get it. Specifically Google and Microsoft are the biggest games in town – though some smaller solutions do exist.
What will switching to these new solutions do for you? How about just to start, the small list of …
- Data reliability and version control
- Automatic saving while you edit (Microsoft Office 365)
- Document storage and sharing with multiple levels of control in the cloud – from public to completely private
- Integrated email solutions
- Website solutions built-in with integration to view / manage content based on personalized security settings per customer or group of customers (again, Office 365)
- Online document editing – no more software installations needed, and you can leave your laptop at home
- Searchable and indexed document storage … never forget where that file is again!
Our money is on the Microsoft solution for now – they were the first to this market space with SharePoint. But, Google is a very close contender and is known for being able to provide reliable and consistent improvements every bit as robust as Microsoft.
This is also just round one. Document management is great … but wait and watch as business software giants such as SAP, Oracle and Microsoft Dynamics begin truely competing for SaaS based online ERP solutions. Both SAP and Microsoft Dynamics have solutions available now … the question isn’t technology. The question is back to presentation and whether you as a user think these systems will be worth adopting for your business model.
In five or ten years it will be interesting to see what features are available to seamlessly and effortlessly manage data without having to be tied to any computer or server. Until then, however, the best way to find the solution that is right for you and your pocket-book is to contact a Network or Software specialist today.
Security has the very specific task of protecting a network from harmful elements that may compromise or corrupt a working infrastructure. An unfortunate truth is that security comes at a cost. The more protective a security blanket is for a network, the more restrictive it also becomes for the users. Never has this been more true than now, as users find themselves needing to remain connected in more ways than ever.
From user interfaces and online ordering to business to business (B2B) data transfers, the world of business relies heavily on allowing incoming and outgoing network traffic. These new features greatly enhance and add flexibility in business models. But these same features add security risk and vulnerability.
What separates good security from great security is not the heavy handed ability to restrict network traffic to a minimum. What makes great security is the offering of reliable protection without the choke-hold of usability upon the users. Biometrics, seamless hardware management, invisibly segmented networks – these tools and more are some of the many options that can be used to create amazing security without the sacrifice of usability.
Just as a bank vault is designed with air holes, a great security expert is as much aware of the user productivity on a network as the potential threats.
- Mira Solutions
Ask most people what they think their biggest concern is regarding email and you will get some key areas repeated consistently.
- Reliable
- Secure
- Remotely available
In all of the years of IT experience here at Mira, each of those areas is easily addressed and handled. The underlying technology behind email is built for reliable and secure transactions (email has also been able to be shared between computers almost since inception for “remote access”). One of the key and mostly ignored aspects of email is rarely addressed, however … Litigation protection.
As we have had clients experience, any legal dispute involving email communication requires that both parties provide a full log of all emails between them. Failure to do so results in penalties and does not look good to the court. In one particular case we have delt with, the emails spanned almost 7 years of communications from a long history of business and a lengthy litigation.
Can you provide 7 years of email history between you and any client, vendor or collegue? Can you provide 2 years of history?
Fortunately there are a lot of solutions and in this current technological environment data storage for history is not limited by “space” on the network. The best solution for you will fit your network size and needs – simply talk with a qualified Network Engineer to find out more.
Your data is the lifeblood of your business – this includes email history. Protect it from every form of threat – whether that threat is lawsuit or virus.
- Mira Solutions
In the 1960′s the only way to do business was face to face. All letters were hand written (or typed). Letterhead was as legally binding as a personal signature. Running a business from the office was mandatory.
While the value in personal time at the office is still high, the time needed to be spent there is less. The network and choice of software can free users and executives from the chain of a desk.
SaaS models allow full productivity from virtually anywhere – even if a laptop is forgotten at home. Secure remote network access can give the freedom to work directly as a network client from anywhere. Cloud storage and web apps free data from the confines of the hard drive while protecting data through redundancy and version controls.
Freedom calls to the business model. Unchain productivity from a rigid time and place and see how companies such as Best Buy have increased their productivity while decreasing their reliance on traditional office use.
- Mira Solutions
In the early 1900′s it was believed that any building built over 12 stories tall in Chicago would simply fall over. This was believed because … It was true. At least it was true back then.
What changed to allow builders to defy this limitation? It was not the building structures themselves – taller buildings were already being built in other parts of the country. It was the foundation. Prior to the World Fair held in Chicago, foundations were simply holes in the swampy ground quickly filled with massive amounts of cement. The methods were crude, but effective “enough”. However, around this time an architect drew up and built what is now standard foundation principles for such soft ground as Chicago – a new tiered style foundation that would actually float on the swampy ground beneath it instead of sitting like a lump of rock.
Much like the easy way of digging a hole and simply filling it up, many networks are put in place quickly and without planning. Each phase, new set of users or expansion is quickly added on with only minimal thought into the long term impact and growth of the network over time. This leads to networks that simply start to subtly fail as they grow – slowing down or stopping service altogether at critical junctions.
A database server not given proper resources at the onset can cause major functions to behave slow or erratic. Improper network backbones can bring what should be fast network response to a grinding halt during peak usage. Many other equally unhappy tragedies lie in wait on networks, ultimately costing much more than the time it takes to prepare and plan ahead.
At the onset of a project – or even as part of an upgrade plan – a network can be setup to succeed. Network engineers can breathe life into server setups, or find ways to remove bottlenecks in traffic – even before problems occur.
- Mira Solutions
