Browse by Tags
All Tags »
ITPRO (
RSS)
I’m excited to deliver, together with the Madeira team, an introductory course on Business Intelligence next month. In this course we will learn: The basics of managing a BI project Dimensional modelling theory and concepts Design and implementation guidelines, using the Microsoft BI product stack, including: The Relational database setup The ETL process, using SSIS Analysis Services and the OLAP model Reporting Services This is an introductory course, best suited to DBAs who want to explore the...
A bit more information regarding the new CALCULATE function in DAX. I’ve described what this function does in a previous post , and I want to expand on how to treat this function in a PowerPivot report. Consider the following report: We have Order Quantity sales ([Order Quantity Total]) and two calculated measures, which we are going to discuss shortly. We have the promotions dimension in the Y axix and 2 slicers: Product Category Name and Calendar Year. The DAX syntax for the calculated...
I’m getting more and more involved in designing PowerPivot models and want to share a bit of DAX tips in the coming posts. I’ve already touched on DAX in previous posts and in this post I’d like to discuss the new CALCULATE and CALCULATETABLE functions and their uses in a model. DAX is a powerful expression language and one of its main differences from the expression language we know in Excel is the fact that we can reference entire tables rather than cells in a spreadsheet. When we need to look...
One cannot ignore the focus Microsoft puts on the Sharepoint platform as of the past couple of years. The Sharepoint product is, in fact, the best server-side product seller for Microsoft ever . I did not fully realize the importance of this platform until recently, when I was attending the “Data Platform” conference several months ago and went to hear Mr. Donald Farmer. Mr. Farmer said that the new workforce, used to network and collaborate using Facebook and Twitter is expecting to find the...
On a previous post I wrote about how the number of databases in an SSAS instance can make an impact on the overall performance of the instance. In this post I want to discuss another, more “explainable” property that you can set in order to improve cube processing performance. Trying to optimize processing time of our databases I set up a performance monitor trace to identify if we are having any resource bottlenecks that we need to address. Looking at the “Physical Disk: Disk Writes Bytes/sec” counter...
I’ve been researching a few performance issues lately on an Analysis Services 2005 instance with 20 (almost) identical databases on it, each weighing roughly 2.5GB and containing 6-7 cubes with approximately 400 partitions in each cube. As you can imagine, this makes up quite an impressive number of files stored on disk. SSAS stores everything on files, and unlike its relational relative (no pun intended), which will store hundreds of GB of data on a fairly small number of large files (I know this...
I was asking the readers to tell us how their SQL Server career started and I though I’d share my story with you as well: I have actually started working with databases when I was a student. I was working at Rambam Medical Center in Haifa in a part-time job as a developer, writing applications in Access and Delphi (?!) with an underlying Oracle database. When I completed my studies I got a job as a Project Manager at Panorama Software Systems . This company manages data warehouse projects and sells...
I had to configure a data file in a very large database this one time and I used Management Studio for that. The file size I was aiming at was 3TB (a very large database indeed). I was quite surprised to see that SSMS has a limit to the size you can configure, which was 2,097,152 MB (i.e. roughly 2TB). I thought I was missing something here and quickly turned to SQL Server Maximum Capacity Specifications to see how big a data file can get, and to found out it is 16TB, not 2TB (the transaction log...
I recently was required to check on the performance of an SSAS instance. We had a new OLAP model with several MDX queries that we wanted to ensure were performing adequately. Now, you may know that SSAS is using caching in order to speed up its response time. Initial queries are directed to disk and once data is first read, it is then being placed in cache to serve future queries faster, eliminating the need to access disk again. What we did first was to clear the cache, using the following XMLA...
I was reading through the (excellent) “ SQL 2008 Upgrade Technical Reference Guide ” paper from Microsoft the other day, which describes in great detail all the best practices of how to go about and upgrade your existing SQL Server 2000 and 2005 installations to SQL Server 2008. I was especially interested in the SSAS upgrade section, as I’m in the process of planning such an upgrade in a highly sensitive and data-intensive SSAS 2005 environment, so I wanted to be sure I was not overlooking anything...
Yesterday I presented at the Israeli BI User Group at Microsoft Headquarters in Raanana. I gave a presentation on the Data Profiling Task in SSIS and how to dynamically configure it and use it to be a part of your ETL workflow logic. I hope the crowd enjoyed it and that I was able to add value to at least some of the attendants. Meir Dudai from “ Valinor ” also gave a presentation about SQL Azure and the Cloud Computing concepts. Cloud computing is definitely one of the coolest buzzwords at the moment...
One of the data flow destination objects in SSIS is the Flat File destination object. You use this destination object when you export data from a source (SQL Server, Oracle, or anything else for that matter) to a flat file. This can come handy when you maintain an Operational Data Store (ODS) in a Data Warehouse environment and you need to store data in text files before loading it to the Data Warehouse database for example or when you need to distribute a segment of data to the outside world in...
In this 4’th instalment of the PowerPivot blog posts series we’ll take a quick look at PowerPivot internals. Firstly, what you need to understand is that a PowerPivot database is similar to an Analysis Services cube, but it is not exactly the same, certainly not in the way it stores data internally within the Excel file. There is an easy way to see what is inside an Excel file (thanks to the crowd at the recent Israeli BI User Group meeting…): simply change the Excel file extension from xlsx to zip...
Not too many people know that they can query internal performance and resource related data for SSAS using DMVs. Yes, I’m talking about similar DMVs to the much more known relational database DMVs, only these DMVs can be queried by connecting to an SSAS instance, open a new MDX or DMX query window (either way will work for you) and type a simple SELECT statement which retrieves the information from the DMVs. These DMVs are actually metadata that is kept internally as schema rowsets and you can simply...
PowerPivot is a combination of Excel and Analysis Services, enabling users to create complex and high-data-volume reports and enjoy the flexibility and usability of Excel alongside the high-performing SSAS engine. In this post I’ll be taking a closer look at a couple of DAX expressions. The DAX expression language is a special expression language which was added to the PowerPivot the Excel 2010 products, and enables the user to query multiple tables (as opposed to the traditional Excel expression...
More Posts
Next page »