BI Course coming up next month…

יום שישי, פברואר 18, 2011

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...
אין תגובות

More on the CALCULATE function…

יום שלישי, אוקטובר 5, 2010

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 () 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 measures is as follows: Order Quantity...
2 תגובות

DAX functions – CALCULATE and CALCULATETABLE

יום שישי, אוקטובר 1, 2010

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 into a series of values in a...
אין תגובות

If you’re a BI professional – You probably should learn Sharepoint…

יום שני, ספטמבר 27, 2010

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 same networks available at the workplace. Using email as the means for communication seems awkward and...
אין תגובות

SSAS Performance tips – Lessons Learned – Part 2

יום שלישי, מאי 18, 2010

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 noticed that it recorded a fairly small numbers most of...
תגובה אחת

SSAS Performance tips – Lessons learned – Part 1

יום שבת, מאי 15, 2010

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 is an over simplification of how one should plan for data file allocation on SQL...
2 תגובות

[OT] – How I started working with SQL Server

יום רביעי, מאי 5, 2010

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 front-end business intelligence software based on the Microsoft platform...
תגיות: ,
תגובה אחת

Database data file size limitation – SSMS

יום שני, אפריל 26, 2010

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 is restricted...
תגיות: , ,
תגובה אחת

SSAS – Cache Usage and Profiler

יום שישי, מרץ 19, 2010

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 command: <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <ClearCache>...
תגובה אחת

Does “Not Supported” mean “Not Possible”? – SSAS 2008 upgrade plan…

יום שני, מרץ 8, 2010

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 important. Initially I was thinking of performing a side by side upgrade, where...
תגיות: , ,
אין תגובות